Linking Two Entries

[ktown18]

Hi everyone,
I have what I think is a simple problem but I am struggling with.

My database reports Number and Qty of objects. Sometimes these can be replaced by existing other objects. Right now i have a form that allows the user to enter the product and qty, or the product and another product that it uses instead (already somewhere in the database). I want a report to show the Product then the qty, then (if there is no qty since it was taken from a previous product) the product it was taken from and the qty of that product. it is fine to have empty fields in the report.

I'm trying to link these using a query but am having touble, and help would be greatly appreciated. Thanks alot!

[June7]

Not sure about data structure. So qty and anotherproduct are two different fields? Is the qty you want for anotherproduct in this same table? Try including this table in a join to itself on the product and anotherproduct fields.

On second thought, that might not work because there are likely multiple entries for anotherproduct? Linking needs to be on unique record ID, not product ID. So the value saved into anotherproduct must be the record ID from this table.

[ktown18]

I know it sounds confusing, There are two fields. Product and Qty. Basically I want the user to be able to input kind of a bypass, where instead of showing a qty, it will be zero for the product and show the product it used instead and its qty.

PRODUCT QTY PRODUCT TAKEN FROM QTY
1024 0 1023 10

Where product taken from and qty is another entry using the same fields in the same table.

[ktown18]

Just to be more clear since I know it doesn't really make sense.

There are two fields. Product and Qty. Basically I want the user to be able to input kind of a bypass, where instead of showing a qty, it will be zero for the product and show the product it used instead and its qty.

Lets say the user is enteryin gthe data for a product "1023." They would enter 1023 with the QTY field 10. Now they want to enter A product "1024." They would enter "1024" but instead of entering a QTY they would enter into a textbox called "Product Taken From." and they would put in 1023. Therefore the report would look like this.

PRODUCT QTY PRODUCT TAKEN FROM QTY
1024 0 1023 10

I need A way to link the unbound textbox "Product Taken from" to the corresponding existing entry and its Qty and to show that in a report.


Thanks for any help!

[ktown18]

I think i have it figured out thanks anyways June7!