Date range based on a record date (1 Viewer)

[JayR]

I am trying to filter out information for an ODBC query I have written, to obviously make it more concise. Because our data is not set up very logically, a lot of my queries are based on text...but that is another story. My current situation starts with a simple make-table query finding certain data within a date range. This is followed up by another simple make-table query to find other specific data within another specified date range. Then I have a cross-tab query between the two tables to locate all the data that shares the same unique identifiers. My problem lies with trying to only pull information from a certain date range. For example, I want information to be included if the data has the same unique identifier as well as falls within +/- 7 days of the received date (dd/mm/yyyy) also listed in one of the tables. Each record will likely have different received dates so I cannot set it as a constant.

Make sense to anyone? I am not confident enough to play around with some expressions...well it is more I haven't been successful at using them correctly.

All help is greatly appreciated.

Thanks,
Jay

 

[namliam]

If you can provide some example of this, I am sure we can come up with something. Atleast it sounds doable.

 

[JayR]

I will give you specific info.....

Pathology laboratory information system database......I pull a list of Cytology cases for 2006.....then I pull a list of Surgical cases for 2006......then I compare the two lists to see what patients have both case types. I am only interested in correlating those cases received within 1 week of each other....probably based on the Cytology cases.
My tables of importance here are "GyneCytology1" and "GyneSurgical1". The field of interest is "collection_date" (each table has esentially the same patient information in in except for case information and results).
The way I had it before (actually still do) in my criteria for table "GyneSurgical1", field "collection_date" is "<[Gynecytology1]![collection_date]" or "[Gynecytology1]![collection_date]". This was giving me all the cases where the collection dates were either the same or the Surgical cases where the collection date was before the Cytology cases collection date. Unfortunately, this includes cases I am not interested in and currently excludes cases that were collected after, but I didn't want to include every case in the report. The collection date is stored and displayed as "dd/mm/yyyy". My computer itself, is also in this short date format.
So if a Cytology case was collected on 08/10/2006, I would want all Surgical cases collected from 01/10/2006-15/10/2006...or something like this.
Any more info needed?

 

[Peter Reid]

Assuming your 'Case Type' fields are called CaseID in both tables;

SELECT GyneCytology1.*, GyneSurgical1.*
FROM GyneCytology1 INNER JOIN GyneSurgical1 ON GyneCytology1.CaseID = GyneSurgical1.CaseID
WHERE (((Abs(DateDiff("d",[GyneCytology1]![collection_date],[GyneSurgical1]![collection_date])))<=7));

 

[JayR]

I have tried this....

And it seems to work as I re-ran the report. Now I have to work on getting rid of all the duplicates, which I thought I prevented in the query and table already.
In Design View, I am confused how it has presented as there is now a new column "Abs(DateDiff("d",[GyneCytology1]![collection_date],[GyneSurgical1]![collection_date]))" with the criteria of "<=7". I thought that it all would have been in the criteria. But if this is merely the difference between the SQL view and Design View I am okay with it.....haven't used straight SQL in a long, long time now since ODBC became the norm for us.
Now, I assume that I can use a similar statement to pull only cases within the preceding 6 months...of course with a different expression....
?????("m",[GyneCytology1]![collection_date],[GyneSurgical1]![collection_date]))" with a criteria of "<=6"

 

[namliam]

Now I have to work on getting rid of all the duplicates,...

In Design View, I am confused how it has presented as there is now a new column "Abs(DateDiff("d",[GyneCytology1]![collection_date],[GyneSurgical1]![collection_date]))" with the criteria of "<=7". ....
Now, I assume that I can use a similar statement to pull only cases within the preceding 6 months...

Well there would offcourse be doubles!
The record which is within your allowed period it will get retrieved multiple times...

The 6 month thing should (offcourse) work, tho generate more doubles.

The way around those double is (possibly) to only check for historical records... but I may be off....

The design view.... well access has to show your criteria some place and the design view works the way it does that columns can be visible or not. Visible columns end up in your select part.
Invisible columns are only there for your Where, Having or Order by clauses.

 

[JayR]

the reason for the duplicates...

is because of the way this database is setup. You would think some kid put it together and not a Lab Information System Vendor. There has been lots of complaints about it from other users as well but the vendor cannot change it on the fly and we were foolish enough to buy the system a few years ago (against my suggestions). In the meantime, I try to pull information that we need for accreditation and put it in a concise format rather than convoluted. This is why I have asked how to use some of the expressions in order to make my life easier as well as try to learn a little more about expressions as I have never been formally shown how to do any of it.

So back to my follow-up query, how would I be able to find the Cytology cases that were 6 mths or less from the resulting Surgical case? To make it even more complex, I could say I am more interested in only those cases from the 6 mths up to 2 weeks.....but I would be happy just to know anything from 6 mths up to the Surgical case and I could just cross out the rest when on paper.

Hope someone could aid me with this follow-up.
Thanks,
Jay

 

[namliam]

It may be me, but all this "shop talk" (i.e. your lab language like Cytology) is confusing my tired and broken down brain.

Can you provide a mock up table with some dates and what you would like the query to report from that mock up table? Seeing some DATA is ussually very helpfull to me....

 

[JayR]

Referring back to Peter Reid's response

He gave me the expression:
WHERE (((Abs(DateDiff("d",[GyneCytology1]![collection_date],[GyneSurgical1]![collection_date])))<=7));
This allowed me to pull the records from table GyneCytology1, which shared a unique identifier with table GyneSurgical1, as well as fell within 7 days of the collected date of specific records (cases) from the GyneSurgical1 table. I don't think I can make it any simpler other than restating what I expect the new criteria to do for me.
As with the criteris Peter created for me, I need to pull records from table GyneCytology1, which share a unique identifier with table GyneSurgical1, as well as fell within the preceeding 6 mths (180 days) of the collected date of specific records from the GyneSurgical1 table.
In my limited ability, I would think that the expression would be similar to what Peter supplied except DateDiff could not be used and the expression may be something like:
WHERE (((?Abs("d",[GyneCytology1]![collection_date],[GyneSurgical1]![collection_date])))<=180));
Obviously.....not true but something I would think like this with proper logic within the expression. Again, it is the same as before with slightly different criteria.

Thanks,
Jay

 

[namliam]

This same code he provided mutated a little.

SELECT GyneCytology1.*, GyneSurgical1.*
FROM GyneCytology1 INNER JOIN GyneSurgical1 ON GyneCytology1.CaseID = GyneSurgical1.CaseID
WHERE Abs(DateDiff([B]"M"[/B]
,[GyneCytology1]![collection_date]
,[GyneSurgical1]![collection_date])<=[B]6[/B];

(I hate surpurfulous brackets)

Should do the trick or???

 

[JayR]

Won't this give me +/- 6 mths?

I changed the value to just <6 and then added criteria in another column to only include records where the collection date for my Cytology were before the collection dates for my Surgicals.....but not the same date as.

Looks like it did the trick.

Thanks very much for the help.

Sometimes you can't see what is fairly obvious.

Jay