If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Comparing 2 tables with date limits and more than 1 grouping level.
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. |
#2
|
|||
|
|||
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... -- Good luck Jeff Boyce Access MVP "Amir" wrote in message ... 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. |
Thread Tools | |
Display Modes | |
|
|