collected related records to populate table

  • Thread starter Thread starter MM
  • Start date Start date
M

MM

I would like to automate the append/update of a related items table
[tblRelatedItems] for our
B2B web. I`m not sure how to collect and identify the "related" items
to append/update.

Our product is books so related items are where the author or series
is the same. The details to located related items are in our Inventory
Table [tblInventory]

Thanks in advance for any help, table details and data sample with
results are below.

[tblInventory]
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...

[tblRelatedItems]
ID
ProdCode
RelatedItem (this is ProdCode for the realted item)
LastUpdated
LastUpdatedBy

SAMPLE DATA with Result

[tblInventory]
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
These titles would create this in related items

[tblRelatedItems]
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
MM
 
Hi

Use an append query like the following to add related items...

INSERT INTO tblRelatedItems ( ProdCode, RelatedItem, LastUpdated,
LastUpdatedBy )
SELECT tblInventory.ProdCode, tblInventoryRelated.ProdCode, Now(), currentuser
FROM tblInventory, tblInventory AS tblInventoryRelated
WHERE ((tblInventory.ProdCode <> [tblInventoryRelated].[ProdCode]) AND
(tblInventory.Author=[tblInventoryRelated].[author])) OR
((tblInventory.ProdCode <> [tblInventoryRelated].[ProdCode]) AND
(tblInventory.Series=[tblInventoryRelated].[series]))

There are 2 approaches...

1) Delete * from tblRelatedItems then run the above

or 2) step 1: expand the above to delete from tblRelatedItems any records
that are no longer related then
step 2: expand the above to insert any records that aren't already in
tblRelatedItems

I prefer the simpler first approach but you may have reasons to use the second

hth

Andy Hull
 
Hi

Use an append query like the following to add related items...

INSERT INTO tblRelatedItems ( ProdCode, RelatedItem, LastUpdated,
LastUpdatedBy )
SELECT tblInventory.ProdCode, tblInventoryRelated.ProdCode, Now(), currentuser
FROM tblInventory, tblInventory AS tblInventoryRelated
WHERE ((tblInventory.ProdCode <> [tblInventoryRelated].[ProdCode]) AND
(tblInventory.Author=[tblInventoryRelated].[author])) OR
((tblInventory.ProdCode <> [tblInventoryRelated].[ProdCode]) AND
(tblInventory.Series=[tblInventoryRelated].[series]))

There are 2 approaches...

1) Delete * from tblRelatedItems then run the above

or 2) step 1: expand the above to delete from tblRelatedItems any records
that are no longer related then
step 2: expand the above to insert any records that aren't already in
tblRelatedItems

I prefer the simpler first approach but you may have reasons to use the second

hth

Andy Hull



MM said:
I would like to automate the append/update of a related items table
[tblRelatedItems] for our
B2B web. I`m not sure how to collect and identify the "related" items
to append/update.
Click to expand...
Our product is books so related items are where the author or series
is the same. The details to located related items are in our Inventory
Table [tblInventory]
Click to expand...
Thanks in advance for any help, table details and data sample with
results are below.
Click to expand...
[tblInventory]
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...
Click to expand...
[tblRelatedItems]
ID
ProdCode
RelatedItem (this is ProdCode for the realted item)
LastUpdated
LastUpdatedBy
Click to expand...
SAMPLE DATA with Result
Click to expand...
[tblInventory]
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
These titles would create this in related items
Click to expand...
[tblRelatedItems]
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
MM- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

Thanks for your post! I'll give it a go and see what happens.

Pat
 
Hi
Click to expand...
Use an append query like the following to add related items...
Click to expand...
INSERT INTO tblRelatedItems ( ProdCode, RelatedItem, LastUpdated,
LastUpdatedBy )
SELECT tblInventory.ProdCode, tblInventoryRelated.ProdCode, Now(), currentuser
FROM tblInventory, tblInventory AS tblInventoryRelated
WHERE ((tblInventory.ProdCode <> [tblInventoryRelated].[ProdCode]) AND
(tblInventory.Author=[tblInventoryRelated].[author])) OR
((tblInventory.ProdCode <> [tblInventoryRelated].[ProdCode]) AND
(tblInventory.Series=[tblInventoryRelated].[series]))
Click to expand...
There are 2 approaches...
Click to expand...
1) Delete * from tblRelatedItems then run the above
Click to expand...
or 2) step 1: expand the above to delete from tblRelatedItems any records
that are no longer related then
step 2: expand the above to insert any records that aren't already in
tblRelatedItems
Click to expand...
I prefer the simpler first approach but you may have reasons to use the second

Andy Hull
Click to expand...
MM said:
I would like to automate the append/update of a related items table
[tblRelatedItems] for our
B2B web. I`m not sure how to collect and identify the "related" items
to append/update.
Our product is books so related items are where the author or series
is the same. The details to located related items are in our Inventory
Table [tblInventory]
Thanks in advance for any help, table details and data sample with
results are below.
[tblInventory]
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...
[tblRelatedItems]
ID
ProdCode
RelatedItem (this is ProdCode for the realted item)
LastUpdated
LastUpdatedBy
SAMPLE DATA with Result
[tblInventory]
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
These titles would create this in related items
[tblRelatedItems]
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
MM- Hide quoted text -
Click to expand...
Click to expand...
- Show quoted text -
Click to expand...

Thanks for your post! I'll give it a go and see what happens.

Pat- Hide quoted text -

- Show quoted text -
Click to expand...

perfect!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

related products table - update query 4
Possible ?? A loop? to populate subform table from an existing table? 1
How to get field value from last related record? 3
How to get from downloaded entries to email more efficiently 5
A Database to organise digitised archive information 0
Need help to find most popular records in 2 related Tables 6
Best way to move multiple authors into Junction table after import 8
Add new record to related table and show data on form 2

Back
Top