Comparing 2 tables with date limits and more than 1 grouping level.

[Amir]

Hi!



I would be happy if you could help me with the following challenge,

which I'm trying to solve for few months.



I have 2 tables:



tblDeliveryPagesEvents:

==================

Field Name: Data Type:

DeliveryPageEventID AutoNumber (PK)

DeliveryPageNumber Text (Lookup1)

ProductInDeliveryPage Text (Lookup2)

Diameter Text

MaterialType Text (Lookup3)

QuantityInDeliveryPage Number



tblReceiptEvents:

=============

Field Name: Data Type:

ReceiptEventID AutoNumber (PK)

ReceiptNumber Text (Lookup4)

ProjectName Text (Lookup5)

DeliveryPageNumber Text (Lookup to DeliveryPageNumber

field in the 1st table)

OrderID Text

ProductKeyInReceipt Text (Lookup2)

Diameter Text

MaterialType Text (Lookup3)

QuantityInReceipt Number

PriceAfterDiscount Number



(There is a reason for the fields like DeliveryPageNumber to be text typed)

*I've marked the lookup fields with numbers in order to show similar
lookups.



The tables which Lookup1 (DeliveryPageNumber) and Lookup4 (ReceiptNumber)
are looking to contain general data about every delivery page and receipt.
Each of these tables (named tblReceiptData and tblDeliveryPagesData) has
date fields, named

DeliveryDate and ReceiptDate.



Now, what I want to do is to compare between the tblDeliveryPagesEvents
table and the tblReceiptEvents table in the following way:



I want to find the differences in quantity between the products in the
receipts and the products in the delivery pages, but I want the query to
distinguish between products that:

1. Have different product key (obvious).

2. Have different diameter value.

3. Have different material type.

So that it won't count products which have the same product key but other
diameter as the same product (because actually in reality they are
different!).

* I would like it to treat non existent records (null..) as 0, of course.



I want the comparison to run for all the Receipts which are between a range
of dates (let's say MinDate and MaxDate), and all the delivery pages which
are linked to the Receipts which are on these dates (As you might notice,
each record in the tblReceiptEvents table has a required DeliveryPageNumber
field).



My final goal is to have a report which shows only the products which their
quantities are different between the receipts and the deliverypages,
seperated by products which has different diameter or material type, and
show the amount of difference.



I've tried to solve it by using many types of queries and subqueries but
with no success..



I know access fine (not an expert), and also VBA for access, but I'm not
strong in the SQL stuff (I know the basics of SQL and eager to learn
though).



I would be grateful if you post a suggestion of a way to solve it by a
query, a report, a combination of both, or a code.



This is not homework or something. It's a problem I'm trying to solve for
about half a year and couldn't succeed by far.



Thank you very much in advance for your help!,

And I appreciate that you've read this long post.



Kind Regards,

Amir.

 

[Jeff Boyce]

Amir

What you mean by describing a data type as (Lookup 1) may not be the same as
what I mean. Are you saying that these "lookup" fields are, in fact, set to
"Lookup" data type in the table definition? If so, please check the
tablesdbdesign newsgroup (and Google.com) for numerous posts about problems
folks have when using the "lookup" data type.

I'm not sure I understand how you wish to compare the two tables -- I don't
understand the relationship between the two tables. From the data structure
you've described, it would appear that an item in the first table could be
"pointed to" by multiple items in the second. Is this the case? If so, I'm
not clear on how you can compare the diameter and other values of a single
record in the first table to multiple records in the second...