Transaction table to update stock table?

**NeoPa** · Aug 26 '18, 01:37 PM

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.

Originally posted by cmo187265

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** · Aug 26 '18, 01:50 PM

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** · Aug 26 '18, 04:59 PM

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** · Sep 19 '18, 12:38 AM

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** · Sep 19 '18, 12:58 AM

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:
tblCardAttribut es - (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)
tblManufacturer s - (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)
tblStorageLocat ions - (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** · Sep 19 '18, 09:40 AM

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** · Sep 19 '18, 05:21 PM

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** · Sep 19 '18, 06:49 PM

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

Code:

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 = PurchaseQuantit y

Phil

**cmo187265** · Sep 19 '18, 11:03 PM

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

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** · Sep 20 '18, 03:23 PM

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 "ContactTyp e" - 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 TblJoinCardSupp lier 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** · Sep 20 '18, 03:50 PM

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** · Sep 20 '18, 06:18 PM

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** · Sep 21 '18, 04:33 PM

Good luck with the move

Phil

**cmo187265** · Oct 1 '18, 12:28 AM

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 "ContactTyp e" - 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 TblJoinCardSupp lier 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!

Attached Files

TBL Layout 5.11.jpg (108.4 KB, 211 views)

Transaction table to update stock table?

Transaction table to update stock table?

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment