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
|
|||
|
|||
Please help with my query
Hello all,
I have two tables: one with data on how much we get charged from our vendors (Table 1) and one with prices from the industry (Table 2). Table 1 has Product Code, Transaction Date, Quantity and Amount, and Table 2 has Product Code, Price Type Code, Date and Price per Unit. Product Code is a key in both tables. What I want to do is I want to create a query to get the Price per Unit (for the transactions with the same Product Code) from Table 2 based on the following criteria: Get the Price per Unit if the Date (e.g. 07-01-08) from Table 2 is close to the Transaction Date (e.g. 07-08-08) from Table 2 and Price Type Code is equal to "B". However, if the Price per Unit is zero for above criteria, then get the price on closest date with Price Type Code is "A". Even there are prices that are Date closer to the Transaction Date with Price Type Code "B", ignore those prices and just pick the latest price with "A". The query that I need would have the following fields: Product Code, Transaction Date, Quantity, Price Type Code, Price per unit and Amount. Thanks. |
#2
|
|||
|
|||
Please help with my query
Sorry for double posting. I thought the first one didn't go through.
Thanks. "Please Help" wrote: Hello all, I have two tables: one with data on how much we get charged from our vendors (Table 1) and one with prices from the industry (Table 2). Table 1 has Product Code, Transaction Date, Quantity and Amount, and Table 2 has Product Code, Price Type Code, Date and Price per Unit. Product Code is a key in both tables. What I want to do is I want to create a query to get the Price per Unit (for the transactions with the same Product Code) from Table 2 based on the following criteria: Get the Price per Unit if the Date (e.g. 07-01-08) from Table 2 is close to the Transaction Date (e.g. 07-08-08) from Table 2 and Price Type Code is equal to "B". However, if the Price per Unit is zero for above criteria, then get the price on closest date with Price Type Code is "A". Even there are prices that are Date closer to the Transaction Date with Price Type Code "B", ignore those prices and just pick the latest price with "A". The query that I need would have the following fields: Product Code, Transaction Date, Quantity, Price Type Code, Price per unit and Amount. Thanks. |
Thread Tools | |
Display Modes | |
|
|