By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,969 Members | 1,564 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,969 IT Pros & Developers. It's quick & easy.

Transaction table to update multiple tables?

P: 21
I have a conceptual question regarding a sports card database I am STILL planning to build.

I have gathered (with the help of other kind members of this forum) that I will need to have a something like a tblTransactions to update my tblCards (via a query I guess) which holds all my unique sports cards.

TransID: AutoNumber PK
TransDate: Date
ContactType: (supplier, customer, both)
ContactName: fk from tblContacts
Transaction Type: (BUY, SELL)

My questions comes because in addition to my tblCards, which I have screen shots of in the link above, I would also like to have a table to store bulk "wax" products like card packs and card boxes that I may buy and sell.

So instead of a collector needing me to search for:
Card - (1989|Upper Deck|#1|Ken Griffey Jr|Green|Autograph|Refractor)

They may want this:
Box - (1989|Upper Deck|Baseball|Retail) from a table like below:

ProdID: AutoN PK
Qty: 1
Year: 1989
Product: UpperDeck
Sport: Baseball
Type: (pack, hobby box, retail box)
# of Packs: 36
Cards/pk: 15

QUESTION (finally!):
Before I research how to actually accomplish this my question is, is it best practice or acceptable to have a tblTransactions to update one of several different Stock Tables based on I guess some update query with different inputs to distinguish which table is to be updated or should I try to combine both stock tables into one tblProducts?

Please visit my link above to understand how different tblWaxCards and tblCards are so you can understand me not wanting to combine them into one stock table.

I definitely appreciate any advice given as I am an Access infant and still learning.
2 Weeks Ago #1
Share this Question
Share on Google+
3 Replies

Expert Mod 5K+
P: 5,380
Could you put the packs in the table cards?
Add to table card types "Hobby Box" etc...
2 Weeks Ago #2

P: 21
Thank a ton for your reply.

I could add a field "Types" to tblCards with field values to be Singles, Hobby Box, Retail Box, Fat Pack, etc...and maybe a quantity field.

There would necessarily be a lot of missing data in other fields that are specific to individual cards like Card #, Card Attributes, PlayerName, etc. if the record were for box of cards instead of an individual cards.

Is this acceptable? I didn't think I was supposed to have missing data in a record...
2 Weeks Ago #3

Expert Mod 15k+
P: 31,347
Having missing data, in itself, is not a problem. It's an oft-used technique to handle flexibility of data.

It's not the final answer on data design, but neither is it something to be too afraid of.
2 Weeks Ago #4

Post your reply

Sign in to post your reply or Sign up for a free account.