related products table - update query

[MM]

I would lilke to automate the update of tblRelatedItemsTable for our
B2B web (Books-Wholesale) there are only two fields ProdCode &
RelatedItem

All the needed info can be found by querying tblInventory the most
straightforward example is an author with 20 titles

ProdCode for title1 is related to ProdCode for title2 --> title20
ProdCode for title2 is related to ProdCode for title1 AND title3 -->
title20
ProdCode for title3 is related to ProdCode for title1 AND title2 AND
title4 --> title20

etc etc

Books in a series would be the same. If someone can point me in the
right direction it would be appreciated.

MM

 

[Guest]

I assume by 'update' you mean insert rows into tblRelatedItems. You'll need
to use an 'append' query which returns the pairs of product codes in two
columns by joining two instances of tblInventory. The only difficulty would
in determining the join criteria between the two instances of the table so
that each row returned represents a valid pair if related items.

Ken Sheridan
Stafford, England

 

[MM]

Thanks for the post...I'm actually looking for some help to ficure out
how to collect the realted titles my inventory fields include the info
but how to collect it and get the related pairs I'm not sure...

 

[John W. Vinson]

Thanks for the post...I'm actually looking for some help to ficure out
how to collect the realted titles my inventory fields include the info
but how to collect it and get the related pairs I'm not sure...

Perhaps if you could describe what you mean by "inventory fields include the
info" someone could help. It's not at all clear how your table is structured
or how this "related" info might be stored currently.

Could you please post a description of your table and some examples of how the
"related titles" are stored?

John W. Vinson [MVP]

 

[MM]

Perhaps if you could describe what you mean by "inventory fields include the
info" someone could help. It's not at all clear how your table is structured
or how this "related" info might be stored currently.

Could you please post a description of your table and some examples of how the
"related titles" are stored?

John W. Vinson [MVP]

THanks for your post, table details are below.

An item is related if the author or the series is the same, we have
ten books by the same author or five books in a sereis. The ProdCodes
for the item and what it is related to do in the related items table.
Thanks in advance for any suggestions!

These titles:
ProdCode Title Author
123456790 Title One Author 1
123456791 Title Two Author 1
123456792 Title Three Author 1
123456793 Title Four Author 1
123456794 Title Five Author 1

Create this in related items
ProdCode RelatedItem
123456790 123456791
123456790 123456792
123456790 123456793
123456790 123456794
123456791 123456791
123456791 123456792
123456791 123456793
123456791 123456794
123456792 123456793
123456792 123456794
123456792 123456790
123456792 123456791
123456793 123456794
123456793 123456790
123456793 123456791
123456793 123456792
123456794 123456793
123456794 123456790
123456794 123456791
123456794 123456792

Inventory Table:
ID
ProdCode
Title
Author
Series
Price
ReleaseDate
Category
SubCategory1
SubCategory2
SubCategory3
many more fields not required for this example last cost, avg cost,
last recipt, last sale etc etc etc...

Related_Items Table:
ID
ProdCode
RelatedItem (this is ProdCode for the realted item)
LastUpdated
LastUpdatedBy

HTH
MM