Originally posted by DBlankGunar, your request is a little vague in some details so I am going to have to make some guesses here.
Tbl1 has one row per itemID CORRECT
Tbl2 also has the itemid field and this is where you are joining to tbl1. CORRECT
Tbl2 has multiple rows per itemid in order to track changing quantities. NO ONE ROW PER ITEM ID QTY GETS UPDATED IN REAL TIME
Tbl2 also has an incremental primary key or datetimestamp in order to determine the most recent record (or quantity). FALSE
If you are using SQL you can handle this by creating a view to replace table2 that only pulls the maximum value of your datetimestamp/incremental primay key per itemid or you can do suppression in the report based on maximum value on the datetimestamp or incremental primary key fields to hide the values you do not want to display.
see this link for help on the suppression process.
Sorry I wasn't clear enough before. My problem is I need a report which shows the following:
ItemID (tbl1) Item Description *(tbl2)
AccessoryItem ID (tbl1) Qty (tbl2)
The problem starts because tbl1 is just a accessory table holding for example Brake disc 1 (Item ID) goes with brake pad 1 (AccessoryItemID). Item ID and Accessory ID are both Items in the tbl2 (one row for each item). I can either link Item ID (tbl1) to Item ID (tbl2) and get only the description, or link accessoryitemid (tbl1) to item id (tbl2) and get the qty.
I want to link both to item id (tbl1) and accessoryitemid (tbl2) to item id (tbl2) to pull my desired information like qty for accessoryitem id and Description for Item ID.
![]()
__tbl1________ _______tbl2_______
Item ID \_________/ Item ID
AccessoryID /
IP Logged
