Effectivity Dates Lookup Query

[Guest]

I have created a query which gives me the information I am expecting but I
suspect is horribly inefficient due to the amount of time it takes to run.

Basically I have 2 tables. One is a reference table where I store DateIn,
DateOut, Product, Location, Length and Width. The other table is where I add
information from a weekly inventory download. The fields in this table are
Date(the date the inventory report was run), Product, Location, Length and
Width.

The reference table will be updated from time to time with new products and
some of the existing products may become obsolete so I reset the DateOut (by
default, DateOut is initially set at 30/12/2040). I am presently using a
Pivot Table form to summarise the data from the weekly inventory download as
to whether it meets the criteria from the reference table or not. If an item
meets the criteria from the reference table 3 weeks ago but does not in this
weeks download, I want to acknowledge that, and that is why I am using the
DateIn and DateOut rather than deleting the item from the reference table.

I am using queries to turn the Product, Location, Width and Length from both
tables into a single string to find a match and this is working OK. Where I
am getting stuck is with the dates. I presently have a calculated field in a
query which checks the date from the weekly download to see if it is >=
DateIn and < DateOut and returns a "Y" or an "N". Because I need to compare
product info at the same time as the date info I am using an inner join
between the 2 tables.

Below is the SQL for the query if that helps at all -

SELECT SW_Data_ExatractPt2.ID,
IIf([SW_Data_ExatractPt2]![Date]>=[SW_Ref_Extract]![Date IN] And
[SW_Data_ExatractPt2]![Date]<[SW_Ref_Extract]![Date OUT],"Y","N") AS SW_Test
FROM SW_Data_ExatractPt2 INNER JOIN SW_Ref_Extract ON
SW_Data_ExatractPt2.SW_Comp = SW_Ref_Extract.SW_Comp2;

With 11,000 records in the weekly download table and 230 records in the
reference table the query already appears quite slow. I am worried as I add
records regularly that it is going to slow down exponentially. Is there a
better way to do the product comparison at the same time as checking for
effectivity dates ?

Apologies in advance for the length of this post, I am trying to cover as
many potential questions up front.

Thanks & Regards,

 

[John Vinson]

I am using queries to turn the Product, Location, Width and Length from both
tables into a single string to find a match and this is working OK.

I'm a bit queasy about this! You can join on up to TEN fields. It is
certainly NOT necessary to concatentate four fields into one string in
order to do a match.

Where I
am getting stuck is with the dates. I presently have a calculated field in a
query which checks the date from the weekly download to see if it is >=
DateIn and < DateOut and returns a "Y" or an "N". Because I need to compare
product info at the same time as the date info I am using an inner join
between the 2 tables.

An Inner Join can be a "non equi-join" - e.g.

SELECT SW_Data_ExatractPt2.ID, SW_Data_ExatractPt2.Product,
SW_Data_ExatractPt2.Location, SW_Data_ExatractPt2.Width,
SW_Data_ExatractPt2.Length
FROM SW_Data_ExatractPt2 INNER JOIN SW_Ref_Extract ON
SW_Data_ExatractPt2.SW_Comp = SW_Ref_Extract.SW_Comp2
AND SW_Data_ExatractPt2.[Date] >= [SW_Ref_Extract]![Date IN] And
[SW_Data_ExatractPt2]![Date]<[SW_Ref_Extract]![Date OUT];


If the {badly named, Date is a reserved word} Date field is indexed,
this should give reasonable performance - certainly far better than a
search on an inefficient IIF!

John W. Vinson[MVP]

 

[Guest]

John,

Thanks for taking the time to reply.

I take your point on the Date field and will adjust.

I used the IIf statement because I am trying to make the slow and painful
transition from Excel and that was what I was familiar with. The multiple
joins and "non equi-join" are news to me. Are there any suggested resources
on this subject I can get a better understanding from ? The Access help files
are fairly unfulfilling.

Thanks & Regards,

I am using queries to turn the Product, Location, Width and Length from both
tables into a single string to find a match and this is working OK.

I'm a bit queasy about this! You can join on up to TEN fields. It is
certainly NOT necessary to concatentate four fields into one string in
order to do a match.

Where I
am getting stuck is with the dates. I presently have a calculated field in a
query which checks the date from the weekly download to see if it is >=
DateIn and < DateOut and returns a "Y" or an "N". Because I need to compare
product info at the same time as the date info I am using an inner join
between the 2 tables.

An Inner Join can be a "non equi-join" - e.g.

SELECT SW_Data_ExatractPt2.ID, SW_Data_ExatractPt2.Product,
SW_Data_ExatractPt2.Location, SW_Data_ExatractPt2.Width,
SW_Data_ExatractPt2.Length
FROM SW_Data_ExatractPt2 INNER JOIN SW_Ref_Extract ON
SW_Data_ExatractPt2.SW_Comp = SW_Ref_Extract.SW_Comp2
AND SW_Data_ExatractPt2.[Date] >= [SW_Ref_Extract]![Date IN] And
[SW_Data_ExatractPt2]![Date]<[SW_Ref_Extract]![Date OUT];


If the {badly named, Date is a reserved word} Date field is indexed,
this should give reasonable performance - certainly far better than a
search on an inefficient IIF!

John W. Vinson[MVP]