Question regarding setting date criteria on queries

[Tiosylanyl]

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.

 

[Tanis]

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]

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.

 

[Norie]

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]

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]

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 ...I just use it, I didn't design it.

 

[Norie]

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]

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]

Sorry for the late reply - Been indisposed the last few days.

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