Transaction table to update stock table?

[cmo187265]

I am trying to build a baseball card database.

Best practice I think would be to have the following tables then have a Transaction Query or Form update all the tables as I buy or sell a card I guess (I need to figure out how to do this but I think I'm supposed to create a separate thread).

DATABASE TABLES -
TransTBL for transactions
ProductTBL for products
LocationTBL for storage location
StockTBL for inventory

MY QUESTION -
In regard to the StockTBL:
How would this work for Baseball cards where inventory quantity for a product will never be greater than 1?

For example I will never have something like this:
StockTBL
Record -> ID55441 / Cal Ripken / #222 / QTY 3

It would instead look like this:
StockTBL
Record -> ID55441 / Cal Ripken / #222 / Grade 9
Record -> ID55442 / Cal Ripken / #222 / Grade 8.5
Record -> ID55446 / Cal Ripken / #222 / Grade 10

I am using this thread as reference (if that's against etiquette please let me know and I apologize in advance)
Inventory Form that adds/subtracts from current inventory number in table

[NeoPa]

First let me start by commending you on the quality of your question. Most experienced members don't manage to lay it out as well as you have. Far from being a problem, linking to another thread is actually encouraged where a lot of information is already laid out.

cmo187265:
In regard to the StockTBL:
How would this work for Baseball cards where inventory quantity for a product will never be greater than 1?

That rather depends on the approach you intend to take. Is each card held uniquely, regardless of its grade, or would multiple cards of the same grade be held in stock as an item with a quantity greater than 1?

If the former then you'd make sure that the individual card is available on your selling form to choose from so that when the stock adjustment is made it can reference that particular stock item specifically.

If the latter then you would simply ensure that the item that's sold/adjusted includes a reference to the grade as well as the name so that the correct stock record is always adjusted.

[NeoPa]

I've added an extra post in the linked thread that you may find helpful as far as the overall understanding of the management of the stock goes.

Best of luck with your project :-)

[zmbd]

NeoPa(...) linking to another thread is actually encouraged where a lot of information is already laid out. (...)

I couldn't agree more with this statement.


I wonder if the card data shouldn't be normalized a bit further

At least
Table to hold player names
Table to hold team names
Table to hold publisher names (Topps, Fleer, Upper Deck, etc...)
? Table to hold card series names (like Diamond, Chrome, etc...)

This way you can account for traded players, different publishers etc...

I'd also consider a table to hold the grade and/or qualifiers
Something like to hold the major three - one could include the 1/2 grades:
[PK_Grade][Descriptive][PSA][SGC]
[1 ][Gem-MT-10 ][10 ][100]
[2 ][Mint ][9 ][98 ]

Personally I'd pull the qualifiers out into their own table and then use a linking table to "tag" the cards

Finally if you want to "Attach" the card image then I would follow this article's advice - the attachment field has some issues, one of which is that it eats the space available for your data at a remarkable rate:
Display images in your DB without using Attachment Fields

If I get time today I'll try to bodge an example together for the tables... no idea where my BB-Card Collection got to or I'd scan an image or two... hmmm...

[cmo187265]

NeoPa, thanks for your kind words. I try very hard to ask good questions but have a lot of difficulty grasping database inner-workings beyond basic normalization. To answer your reply...each card will be held uniquely regardless of Grade. That said, I will give your first recommendation some thought to see if I can understand completely before replying again. Thank you very much for your help!

[cmo187265]

zmbd, Thank you for your reply to my question! It confirmed to me that my tables were set correctly.

To follow up on your normalization suggestion here is what my tables looked like if you would like to review:
tblCardAttributes - (serialed, mem, auto, error, etc.)
tblCardColors - (blue, green, etc)
tblCardEffects - (Foil, Holo, Refractor, etc)
tblCardTypes - (Base, Insert, Subset)
tblGradeTypes - (PSA, BGS, Raw)
tblsGradeScales - (1-10,1-100, gem mt, nm mt, etc)
tblManufacturers - (Bowman, Topps, etc.)
tblPlayers - (pkPlayerID, name, fkPosID, DOB, fkSportsID)
tblSports - (pkSportID - MLB, NFL, Non, NHL, etc.)
tblPositions - (pkPosID: 3B, P, PF, SF, QB etc.)
tblTeams - (pkTeamID, city, team name, etc.)
tblCards - (ProductTBL: that brings everything together)
tblStorageLocations - (LocationTBL: Album1, ALbum2, box3, etc.)
tblTransactions - (TransTBL: pkTransID, fkCardID, TType, TDate)
tblCustomers - (who I sell cards to)
tblSuppliers - (who I buy cards from)

I struggle more with understanding inner-workings...how to set up queries and forms to get things moving. While I love the normalization part it kills me that I don't have enough knowledge to start entering data already unless I just start adding data to the ProductTBL manually!

Really appreciate everyone's help!

[PhilOfWalton]

You've done really well with your normalisation.
It's worth just checking a few points with your "lookup" tables though.

So if we consider say tblCardTypes. I presume CardTypeID is an AutoNumber. CardType (Base, Insert, Subset) should have the properties set to Required - Yes, Allow Zero Length - No, Indexed - Yes (No Duplicates)

If we consider say tblPlayers. I presume PlayerID is an AutoNumber. Name is a reserved word, so I suggest you change it to PlayerName. Usually one would have PlayerFirstName and PlayerLastName instead (easier for searching), both of which should be set to Required - Yes, Allow Zero Length - No.
Now using the Indexes, you can create a combined index consisting of PlayerFirstName and PlayerLastName, and that can be set to "Unique"

These methods should apply to all the tables. Particularly it is worth deciding which fields are required, and which fields aren't.

For the moment, I am going to ignore the TblTransactions, we will come to the buying and selling aspect in a further post.

In the mean time, see if you can create a form based on TblCards, with lots of ComboBoxes where you select the colour, type player, team etc., you will be well on the way.

Advise us of how you get on.

Phil

[cmo187265]

Thank you for your reply Phil! I appreciate the support.

I would like to answer your questions:

tblCardTypes:
- not Autonumber as there are a small finite number of options here. Is this OK?

tblPlayers:
- no reserved names used. I should have been more descriptive in my post. I use PlayerNames as you suggest.
- PlayerID is autonumbered
- I did not separate FirstName and LastName as is typical in normalization. I did this to account for (especially in baseball) players of different cultures that have different naming structures. As well in baseball, often records were kept using a players nickname instead of their FirstName LastName convention. Examples : Carlos de La Cruz, Home Run Baker, Shoeless Joe Jackson, etc.

Form for tblCards:
I will do more research and attempt to create a Form. So I understand your suggested next step, am I creating a Form that will eventually be used to enter data into the tblCards? Should I begin linking lookup tables to the tblCards at this stage?

Thank you all for your help!

If I am breaking any type of forum etiquette in my questioning please kindly let me know. I assume my conversations now are all still driving to my original question regarding Transactions in Access.

[PhilOfWalton]

Firstly, I must admit to knowing absolutely nothing about Baseball, and considerably less about Baseball cards, untill I had a look to see what they were. Looks as if it could be quite a lucrative interest.

I tend to always have an Autonumber primary key in nearly all my tables – not essential, but it does no harm. Then in my main table, I have fields with exactly the same names as the PK in the lookup tables.

I fully understand the situation with PlayerNames Just ensure that they should be set to Required - Yes, Allow Zero Length – No, Indexed – Yes (No Duplicates)

Have you set up your Relationships yet? If so, it would be helpful if you posted an image, showing all the tables and all their fields, with as little unused “white space” as possible so that we can see a clear image.

As I said, I know nothing about Baseball cards, so before I can advise you on your form design, my question is how are the cards sorted. I presume they don’t have a reference number, so when you go to buy a card, what do you look for? E.G. is it the manufacturer, the Payer Name, the Team, the date etc. We need to get these in order so that the list of cards is in some sort of logical order.

Again, jumping ahead, I note your concern about Transactions. I will just deal with the Purchase transaction as the Sales transaction is almost identical. I am assuming you have both customers and suppliers that you deal with on a regular basis, as well as “one offs”.

We have a combined primary key consisting of CardID & SupplierID

TblJoinCardSupplier
    CardID            Joint Primary Key
    SupplierID        Joint Primary Key
    PurchaseDate
    PurchasePrice
    PurchaseQuantity
    Type            What is this?
    Etc

Although you state that the quantity will always be 1, having a number there does allow you to “bulk buy?????”

The beauty of this is that very simply, you can see what each Supplier has sold you and Price, Date and any other details.

On your main form, you will have 2 Subforms, one giving the Supplier transaction and when you sell the card, the second subform will give the Sales Transaction.

Your stock is obviously the difference between the 2 Quantities, and if the Customer transaction doesn’t exist then the Stock = PurchaseQuantity

Phil

[cmo187265]

Thanks again Phil!

I will answer your questions in turn.

Q1 - Customer Searchables
Q: How would Collectors/customers search for cards?

A: Here is an example of a simple fictitious baseball card.
1998 Bowman Chrome #231 Ken Griffey Jr.

Set Collectors - search for 1998 Bowman Chrome #231.
Player Collectors - search for Ken Griffey Jr.
Team Collectors - search for Mariners and possibly by yr
Some Collectors collect Autograph cards or Variants:

1998 Bowman Chrome #231 Ken Griffey Jr. Green Auto #24/50

Now the simple example above includes the following collectible attributes:
1) a Green variation
2)an Autographed
3)a serial number of 24/50.

Notes:
Bowman is the Manufacturer or Brand.
-#231 is the card number in the Bowman Set released that year.
-Mariners is the team he is playing on pictured in the card.
-Collectors will not access my database. I will do it for them.

Instead of thinking of baseball cards as widgets that you can bulk sell, think of them as sports cars. Collectors may collect European, American Muscle, or more specifically Mustang, Ferrari, or Red Mustangs or Blue Sports cars with special badging/emblems from 1965.....

Q2 - Transaction Type
Q: Transaction Type - What is it?
A: I listed TransType to hold a BUY/SELL option field.

Q3 - Access Tables Screen Shot
TBL Layout.jpg


I am OK waiting on transactions if your guidance suggests me to do so, I am only concerned that I keep the line of questioning focused for the sake of forum etiquette. I am new to forums and unsure on whether to start a new thread or whatnot.

Thanks again for your help!

[PhilOfWalton]

Sorry for the delay in replying, I am based in the UK and I suspect you are in the US, so by the time you get home in the evening, I am tucked up in bed.

So I am getting to grips with the card details

I have had a look at your relationships, and would make the following comments:-
CardEffects & CardTypes should use a CardEffectsID & CardTypesID as the primary key linked to the TblCards, so these fields (Number - Long) should be in TblCards, rather than a text field that you have at the moment (Table takes less space using a long number rather than text, and searches are faster)

You are missing "Color" in your TblColours.

You need to enforce referential integrity between ALL the linked tables. This will ensure that when you say want to select a storage location for a card, that location must already exist. Just as important is that if a particular storage location is used, you can't delete it.

We can simplify the Db by combining the TblCustomers & TblSuppliers into a single table - TblContacts with an identical layout. Add an additional field "ContactType" - Number - Byte and set it to 1 = Supplier, 2 = Customer, 3 = Both. I am guessing you may deal with some people both buying and selling.

Then the TblJoinCardSupplier mentioned in a previous post will have the TransactionType set to "Buy" or "Sell". This will often be set automatically, because you will select a contact from a combo box for your transaction. If the contact is flagged as a Customer, the TransactionType will be "Sell", and if the contact is flagged as a Supplier, the TransactionType will be "Buy". If the flag is for both, then to a certain extent you need to select the TransactionType manually.

However remember, that if you haven't bought a card and got one in stock, you can't sell it, and equally (I still have trouble believing that each card ever produced is unique) if you have a card in stock, you can't buy a new one.

That way you will need a single subform for the transaction. The other advantage is that you can look at a Contact and see all the Buy & Sell transactions.

Where does the TblGradeTypes fit into the plot?

Phil

[PhilOfWalton]

Just noticed that you have no primary keys defined.

All the ....ID should be AutoNumbers and set as Primary Key.

The corresponding fields in the TblCards should be Number - Long Integer. (An AutoNumber is just a Long Integer that increments by 1 every time you add or attempt to add a new record - hence it's uniqueness). Generally the AutoNumber Prime Key is of little interest to the user, but is used by Access to keep track of everything.

I would like to see your modified Relationship image after you have modified it. It is obviously essential to build the Db on a sound foundation.

Phil

[cmo187265]

Thanks for your replies Phil! And no worries on timing as I greatly appreciate your responsiveness along with others who have replied to my posts.

I will edit this post with additional content next week in consideration of your latest suggestions. I will be moving all weekend so will likely not have time to work on this until next week. As well, I would like to take some time to look into your suggestions in detail before implementing because I want to understand your suggestions as well instead of just blindly applying your advice. I sit pretty low on the y-axis of the learning curve for this stuff right now :)

[PhilOfWalton]

Good luck with the move

Phil

[cmo187265]

Hey Phil! The move went well...now we need to unpack!
I hope all is well with you.

Getting back to your suggestions/questions:

PhilOfWalton:
I fully understand the situation with PlayerNames Just ensure that they should be set to Required - Yes, Allow Zero Length – No, Indexed – Yes (No Duplicates)

Are we sure we shouldn't allow duplicates? If not, how do we handle players with the exact same full name?

PhilOfWalton:
We can simplify the Db by combining the TblCustomers & TblSuppliers into a single table - TblContacts with an identical layout. Add an additional field "ContactType" - Number - Byte and set it to 1 = Supplier, 2 = Customer, 3 = Both. I am guessing you may deal with some people both buying and selling.

Genius idea! I was able to set to Number - Byte but I was unable to figure out how to set the bytes 1,2,3 to specific values. Could you elaborate?

PhilOfWalton:
Where does the TblGradeTypes fit into the plot?

I am still trying to normalize this. Individual cards are graded by third party appraisers. The big 3 are BGS, PSA, SGC but there are many others. Some collector's only collect a certain Grading Appraiser/Service. BGS grades from 1-10 with .5 increments. PSA is the same as BGS but don't have a 9.5 grade. SGC grades from 1-100 in 1 pt increments. TblGradeTypes is an attempt for me to make a table with the Grading companies and their common abbreviation (e.g. Beckett - BGS).


I added TblJoinCardSupplier but was unsure of how to create a Joint Primary Key. I have two foreign keys CardID and ContactID and an Autonumber Primary Key. Is this correct?

Other than that, let me know what you think of my tables after enforcing referential integrity and making all PKs Autonumber (attached).

I also added a added a tblPlayerYR. A large majority of collectors only collect cards from a players rookie season or prospect cards when they where in amateur leagues.

Let me know your thoughts. Excited for your responses. Thanks again for your help with everything!

[PhilOfWalton]

Ok, I'm going to pass the ball back to you.

If you have 2 players with the same name, how do you differentiate between them?

Don't understand your problem with ContactType. A byte type number can be any whole number between 0 and 255, so for the moment, in your table, just enter 1, 2 or 3.

No, you haven't got TblJoinCardSupplier quite right. Firstly temporarily remove the joins to TblCards & TblContacts.
Then delete the Field "ID".
Highlight both CardID & ContactID simultaneously, and press the Key symbol. That creates a joint key.
Then re-establish the joins.

Assuming that a card can be graded by more than 1 appraiser, you need to set up a similar arrangement with Join table as above.

TblJoinCardGrader
    CardID        Joint Primary Key
    GraderID      Joint Primary Key
    GradeValue    Decimal

See how you get on.

Phil

[PhilOfWalton]

Just noticed that the Players / Position relationship is not correct. I leave you to sort that out yourself.

Phil

[cmo187265]

Thanks Phil.

PhilOfWalton:
If you have 2 players with the same name, how do you differentiate between them?

I would differentiate by another field, likely DebutDate. DOB would be better but that data is hard to come by. With that said, I still should not allow for duplicates in the PlayerName field?

PhilOfWalton:
Don't understand your problem with ContactType. A byte type number can be any whole number between 0 and 255, so for the moment, in your table, just enter 1, 2 or 3.

Everywhere I looked just described what the data type is but not how to set a byte to a certain value (e.g. 1 = Supplier). Is this something I do in the properties sheet or are you just asking me to create 3 "dummie" records for the time being and set the ContactType field to 1, 2, and 3 respectively?

TblJoinCardContact
Thank you for helping me on TblJoinCardContact. My searching for Joint Primary Keys continued to land on creating Composite Primary Keys which isn't the same thing and was instead about creating a calculated Primary Key. I will update per your clarification to me.

PhilOfWalton:
Assuming that a card can be graded by more than 1 appraiser, you need to set up a similar arrangement with Join table as above.

Just to clarify, a card can be graded by only 1 of many appraisers. The cards are "slabbed" in the appraiser's proprietary tamper proof case design after they are appraised. Consider fictitious cards below as an example.

Record -> ID55441 / 1982 / Fleer / Cal Ripken / #222 / PSA 9
Record -> ID55442 / 1982 / Fleer / Cal Ripken / #222 / BGS 8.5
Record -> ID55443 / 1982 / Fleer / Cal Ripken / #222 / SGC 96

PhilOfWalton:
Just noticed that the Players / Position relationship is not correct. I leave you to sort that out yourself.

I assume you want me to have the PositionID as an Autonumber PK and as a FK in the tblPlayers. I will fix this. I already have imported data in the tblPlayers which includes traditional abbreviations that I don't want to lose but I will figure it out and update you.

Thank you again for your help.
Chris

[PhilOfWalton]

Hi Chris

We're making progress.

With the player name, yes you can allow duplicates, but it is nice to have some additional information such as DOB or possibly, more likely, Position, to prevent you adding the same player twice. By "same player", I mean just that, not 2 different people with the same name.

Sorry, I confused you with the "ContactType". It is just a value you enter into the table in Datasheet view, together with the name, phone no, email etc. Yes we are talking "Dummy Records".

So if you say there is only 1 grader per card, in your TblCards, you need a GraderID (Foreign Key) and a GradeValue. The Grader will be picked from a Combo Box, and the GradeValue held in the TblCards. So ignore the bit about TblJoinCardGrader.

Hope that clears things up.

Phil

[cmo187265]

Thanks Phil. I do appreciate you making me work for it a bit but also appreciate your clarifying when my searches are not fruitful.

ContactType - "Dummy Record"
I attached a screen shot below.
1. Should I set the phone number to a certain format or input mask or something of that nature?
2. Should I further normalize the address fields into multiple fields and would I do so in the same table or create an additional table - tblAddresses?

GradeTBL
You mentioned using a GraderID as a FK in tblCards. Would this be an Autonumber or a more recognizable value since it's being selected from a combo box? Hopefully my screen shot below clarifies my question.

I wasn't able to get to everything tonight but will edit this post when I have time tomorrow to fix a few more things discussed in your suggestions above.

Sincerely appreciate the progress.

[PhilOfWalton]

Sorry for the delay in replying.

Formatting phone nos. You can use an input mask, but are all your contacts in the US, if not, do they use the same format.

Your contacts: I always split into FirstName and LastName, because it is very easy to concatenate fields, and can be more difficult splitting fields.

I'm not sure whether the ContactBuisiness is a duplicate of the ContactType. will a 1 always be "CardSupplier", 2 = "Card Customer" and 3 = "Card Guys"?

You don't need a TblAddresses, unless you have a number of contacts living at the same address. However, if you have a concentration of contacts living in one City or State, you may wish to create a TblCities & TblStates, with the FK in TblCites = PK in TblStates. That way, having selected the City from a Combo Box, the State will appear automatically.
I would suggest several lines for the addresses including CityID and zip code. Look at your addresses and basically have one piece of information per address line and decide.

No your TblGrades is wrong

TblGraders
    GraderID    AutoNumber    Primary Key
    GraderAbbr  Required - Yes, Allow Nulls - No Indexed - No Duplicates
    GraderService      Required - Yes, Allow Nulls - No Indexed - No Duplicates

In your TblCards, the matching Foreign key is GraderID Number Long.

The Combo Box to select the grader will either show
BGS Beckett Grading Service
or Beckett Grading Service BGS
depending on the order you select your field.

Phil

[cmo187265]

Sorry for my late reply Phil. A work project has been consuming a lot of my time.

PhilOfWalton:
I'm not sure whether the ContactBuisiness is a duplicate of the ContactType. will a 1 always be "CardSupplier", 2 = "Card Customer" and 3 = "Card Guys"?

Yes, I realize you couldn't have been sure because I was so unclear. I have updated via a screen shot below so you can see the difference between the 2 fields now. The numbers will be as you suggested in your previous post; 1 ="supplier", 2 = "customer", 3 = "both".

TblGrades
GradeAbbr
GradeService

For these fields above I can set your suggested parameters via DesignView > General > Field Properties.
I was able to set Required - Yes, Indexed - No, but I could not see a parameter for Allow Nulls. There is an Allow Zero Length parameter. Is this what you are looking for?

I believe my tables are finally correct so feel free to take a look when you have time.

Thanks Phil.
Chris

[NeoPa]

I believe that Nulls are only allowed when Required is set to No. I suspect (but I can't be sure) that Phil was referring to Allow Zero Length which determines whether or not zero length strings (ZLSs) are allowed.

So, Required=Yes and Allow Zero Length=No means that neither Nulls nor ZLSs are accepted.

[PhilOfWalton]

Sorry, I should have said Allow Zero Length = No.

Neopa is spot on in his explanation.

Phil

[cmo187265]

Neopa, Phil, thank you both. I'm excited about learning all this to get to the answer of my original question. I again will be delayed in my response so I apologize. The battery in my Dell XPS has swollen so I am waiting for feedback from Dell on if it's safe to turn my computer on. Hopefully i can turn it on and grab the file to an external HD so I can continue working on this while my replacement battery comes. I will update asap.

Chris

[NeoPa]

That's fine Chris.

We have a bunch of threads to keep us busy ;-) We just tend to deal with them as the responses come in. Relax and get to it when you can :-)

[cmo187265]

Phil, NeoPa, thanks for your patience while my computer was being repaired. I hope you are ready to get back into this?!

Per your suggestion I have set tblGrades fields to reflect the following:
Required = Yes
Allow Zero Length = No
Indexed = Yes (No Duplicates)

Should I be setting similar parameters for all fields that aren't a PK in my Database?

I have also attached an updated screenshot of my Database relationship view.

Excited to get back in the swing of things!

[NeoPa]

Hi Chris.

As you may have seen already, I'll just jump in when I can clarify a point of general Access knowledge. I'll leave you otherwise in Phil's capable hands but I'll continue to monitor any new posts as they're added.

[NeoPa]

It turns out I can clarify how best to use PKs and FKs.

PKs - Primary Keys.
These should always be indexed without duplicates as they are, by definition, always unique. Setting a field as a PK in Access automatically adds such an index for you titled PrimaryKey. If you set it separately by choosing Indexed = Yes (No Duplicates) before adding it as the PK then it will create a duplicate entry for you with a separate name. Not generally a good plan ;-)

FKs - Foreign Keys.
These are generally indexed but, except in very unusual circumstances, as Yes (Duplicates OK). Many different records can point across to the same record in the other table. Consider [tblOrder] & [tblOrderLine] for instance. No prizes for guessing what these tables hold. Each of the records in [tblOrder] has a PK. Each of the records in [tblOrderLine] also has a (different) PK, as well as an FK that matches one PK from [tblOrder], to show that it belongs to one, and only one, order. That way all orders and lines are linked together seamlessly.

Essentially an FK should always match the PK from a table that is generally a separate one. Self-referencing tables, where FKs link to PKs in the same table, are a story for another day. Think about handling family relationships within a [tblPerson] table.

[cmo187265]

NeoPa, thanks for monitoring and jumping in from time to time. I will review your suggestions on PKs and FKs to ensure my Database matches.

Thanks again.
Chris

[NeoPa]

Excellent :-)

Otherwise I'll leave you in Phil's capables. I expect he'll jump back in when he gets back from wherever he is.