Question regarding setting date criteria on queries

Tiosylanyl

New Member
Joined
Jul 31, 2006
Messages
48
Hello,

I have two tables that are linked by a "client" ID number. One table represents every shipment we have made to a client, the other table represents clients that have been discharged from our services, and the dates they were discharged.

I am trying to set up a query such that I can pull all the patients discharged within the last 12 months and then pull data on all shipments for the twelve months prior to the client being discharged.

I know a round about way of doing this, but it requires a lot of manipulation and time that I may not always have. I was wondering if anyone had any ideas on how to set this up on a single query to cut down on time investment in doing this.

Thanks in advance.
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

Tanis

Active Member
Joined
Dec 8, 2002
Messages
305
Why can't you run two queries. The time involved in both creating them and running the would take hardly any time at all.
 

Tiosylanyl

New Member
Joined
Jul 31, 2006
Messages
48
I suppose where I'm being stumped is that two clients could have two separate discharge dates (i.e. one discharged 8/31/2006 and one discharged 5/30/2006), and that I'm trying to get shipment data for all shipments 12 months prior to each discharge date (i.e. 9/1/2005-8/31/2006 for discharge one, and 5/31/2005-5/30/2006 for discharge two).

Running a query for my list of discharges for the last 12 months is the easy part - doing a second query to collect shipment data based on set criteria for shipment dates (12 months), but personalizing it to the discharge date is where I'm finding it tricky (keep in mind that we're talking about 1000-2000 clients too).

I'm sure there is just something basic I'm missing. :oops:
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,280
Office Version
  1. 365
Platform
  1. Windows
Why do you have 2 different tables for what is essentially the same sort of data?

Why not just add a field for discharge date?
 

VicRauch

Well-known Member
Joined
Mar 23, 2006
Messages
2,032

ADVERTISEMENT

Run the first query that would give you client ID and discharge date, then run the second query where you set the from and to dates using the discharge date and discharge date - 1 year. That way you should get all the shipments for the year prior to each client's discharge date.
HTH,
 

Tiosylanyl

New Member
Joined
Jul 31, 2006
Messages
48
There are two different sources for the data. The data for discharged clients come from a report generated out of our primary database. Data for shipment information (i.e. units, revenue, etc.) is kept in a back end database maintained by our finance department and is not intended to track clients that are discharged.

The only true commonality between the two databases is the client ID number.

I know, not efficient :confused: ...I just use it, I didn't design it.
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,280
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Still don't see why you can't have 1 table.:)

If you have 2 different sources you should easily be able to combine them using a query, joining by the Client ID number.

You could then either use that query to make a table, or just use it as the source in your other queries.
 

VicRauch

Well-known Member
Joined
Mar 23, 2006
Messages
2,032
You asked:
Running a query for my list of discharges for the last 12 months is the easy part - doing a second query to collect shipment data based on set criteria for shipment dates (12 months), but personalizing it to the discharge date is where I'm finding it tricky (keep in mind that we're talking about 1000-2000 clients too).
I responded with:
Run the first query that would give you client ID and discharge date, then run the second query where you set the from and to dates using the discharge date and discharge date - 1 year. That way you should get all the shipments for the year prior to each client's discharge date.
I guess I was not very clear. Sorry. By using the 1st query as the list of discharged clients, and joining the 2nd table (of shipments) to the first query using clientID, and asking for the shipments, by date, where the date is between DischargeDate and DateAdd("y", DischargeDate, -1) I would think that would give you what you were asking for. Because DischargeDate is connected with the ClientID, you will get the last 12 months of shipments per client this way.
???
 

Tiosylanyl

New Member
Joined
Jul 31, 2006
Messages
48
Sorry for the late reply :oops: - Been indisposed the last few days.

Thanks for the advice guys. I did end up managing to work it out using VicRauch's approach.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,553
Messages
5,636,979
Members
416,953
Latest member
broexc

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top