Help with simple design

[Davebert]

I will be surprised if in the future you are not needing related tables. Once you get this working you will likely want more functionality or track more information.

Maybe you have multiple images for each item (top view, left side view, right side view)
Now you need a table that is a "Child Table" because one item can have multiple images.
Or maybe you want to track multiple notes. A child notes table.
Or maybe you do need information about the brand. Related brand table.

Oh yeah, I will find a way to learn how to make a relational database if it kills me!
I will start with June7's instructions just to learn something and later on I may have the skills to better understand this stuff.

 

[Gasman]

A simple music collection would be enough to learn. Most people have a music collection.
You would have
TblArtist
TblAlbum
TblTrack
TblMusicType CD,lp,cassette etc

 

[MajP]

Microsoft has a template called Northwind 2.0 when you open a new database. Several members here contribute to this template. This template is made as a demonstration of capabilities. You can create a database from this template and play with it.
Look at the tables and the relationship window. Then you can understand how this design supports the whole db.
Also might want to review this

Relationships1

This article discusses the differences between query joins and table relationships and explains the important use of table relationships to enforce referential integrity. Three methods of adding relationships are discussed. The article also explains how Access uses the MSysRelationships system...

isladogs.co.uk

 

[June7]

The 3 additional tables are "lookup" tables. They provide a means to dynamically build lists for comboboxes or listboxes. These input controls assist with maintaining data integrity.

Either save record autonumber key into tblFilaments instead of text because numbers take less storage or save text such as "White1" but do it from a combobox list so user doesn't accidentally type "Wite1" and data is then crap. These would be BOUND comboboxes/listboxes for data entry - UNBOUND controls could be used to build search criteria.

These names are likely to always be short and your database small so saving text is not a great impact.

If you do save text then probably should designate the name fields in each lookup table as primary key and autonumber key goes away (or leave it but remove primary key designation). Then set up a relationship in Relationships builder to "Enforce Referential Integrity" and "Cascade Update Related Records". So if someday you decide to change "White1" to "Alabaster", all existing tblFilament records with that key will automatically reflect that change just by changing the key value in tblColors.

My software source was https://flixeasy.com/?_kx=BJjRo1iQaGhsJgXPvjYXIyNzaOOcZDXYmnYcfI7ieUw.RVRJPx

 

[Davebert]

Here is where I get messed up. I really don't understand fully which fields are set to foreign keys and how to link them correctly. Image to show and to have you comment on if it is wrong or haw ro connect them properly. I know how to make the connections but I always get errors or just plain don't understand how to connect them correctly.
Thanks

 

[Davebert]

A simple music collection would be enough to learn. Most people have a music collection.
You would have
TblArtist
TblAlbum
TblTrack
TblMusicType CD,lp,cassette etc

That's a good idea but I remember over 20 years ago studying the one from Microsoft Access that had Chai tea in it. I was confused and burnt out then and a little bit now as well. But it's worth a revisit to a music db.
Thanks

 

[June7]

As I said, which field is designated as primary key depends on what value you want to save into tblFilaments.

These lookup tables each relate to tblFilaments, not directly to each other.

In that image ColorID and BrandName are designated as primary keys. Those are the values that should be saved. Each should be linked to tblFilaments which you don't show in that image. Links must be on same type of fields. Assuming ColorID is autonumber type so it must link to a number (long integer) field in tblFilaments. BrandName is text (Short Text) so must link to text (Short Text).

Advise you to be consistent in design choices.

For example of a search tool, look at http://allenbrowne.com/ser-62.html
Allen has a lot of interesting stuff.

 

[Davebert]

As I said, which field is designated as primary key depends on what value you want to save into tblFilaments.

These lookup tables each relate to tblFilaments, not directly to each other.

In that image ColorID and BrandName are designated as primary keys. Those are the values that should be saved. Each should be linked to tblFilaments which you don't show in that image. Links must be on same type of fields. Assuming ColorID is autonumber type so it must link to a number (long integer) field in tblFilaments. BrandName is text (Short Text) so must link to text (Short Text).

Advise you to be consistent in design choices.

For example of a search tool, look at (removed because the forum wouldn't let me post my reply)
Allen has a lot of interesting stuff.

Ok, I think I need to slow down and re-read and play around and absorb all this good info and let it sink in. I appreciate all the help so far and will be working hard to get it done. Also, thanks for the link to the Access deal. I bought the 2021 retail version for $9.99 USD and am downloading it now. What a deal! Then I will start using ACCESS to get this going and come back tomorrow if I have any more specific questions. Thanks for the link for additional info too. I appreciate it.

 

[MajP]

Its 2025. No need to read just grab a glass of wine and watch some videos. There are lots. This guy is pretty clear and understandable.

 

[Davebert]

Its 2025. No need to read just grab a glass of wine and watch some videos. There are lots. This guy is pretty clear and understandable.

I actually had this exact video open on one of many tabs in my browser yesterday but didn't have Access yet so I only watched a few minutes of it. Now it will make more sense. And as a matter of fact, I used to do a little VB6 coding so all that form stuff is going to be easy for me adding controls etc. The guy in the video is good. No wine for me though as I quit drinking over 2 years ago just in the interest of living as long as I can without too many possible medical issues in the future.
Thanks

 

[June7]

Lucky you, price went down. But maybe could have saved that $9.99

I am not sure you will notice any change from 2010. This lists what's new but says nothing about what was removed https://support.microsoft.com/en-us...ess-2021-2c5c0766-b22b-4b81-a222-a791a8b5b54b

Field type names for text and memo changed to Short Text and Long Text sometime after Access 2010 as well as dropping CollectData and pivot objects. Hopefully you don't miss those features.

 

[GaP42]

In the table I have the following fields:

ID Integer Autonumber
TYPE VarChar
COLOR VarChar
BRAND VarChar
TD Integer
HEX VALUE VarChar
QUANTITY Integer
NOTES VarChar

Have not seen it mentioned - but Type is a reserved word and should not be used as the name of a field.

and it is recommended that ID is descriptively named - eg FilamentID - to prevent misunderstanding with any other ID fields that are found in other tables (when you decide you need them)

 

[MajP]

Type is a reserved word and should not be used as the name of a field.

To clarify Reserved words are words you should not use for naming Access Objects (tables, fields, forms, controls, queries, reports, etc.)
This can cause conflicts and extra work to avoid them.

List of reserved words in Access - Microsoft 365 Apps

Lists the reserved words that you should not use in field, object, and variable names. It is not practical to provide a list of all reserved words, such as built-in function names or Access user-defined names.

learn.microsoft.com

 

[Davebert]

Lucky you, price went down. But maybe could have saved that $9.99

I am not sure you will notice any change from 2010. This lists what's new but says nothing about what was removed

Field type names for text and memo changed to Short Text and Long Text sometime after Access 2010 as well as dropping CollectData and pivot objects. Hopefully you don't miss those features.

Thanks for the added info. I just got it installed so I have some catching up to do. I don't think I will miss those features from v2010 because I just bought it yesterday and it's in the mail and on it's way.

Have not seen it mentioned - but Type is a reserved word and should not be used as the name of a field.

and it is recommended that ID is descriptively named - eg FilamentID - to prevent misunderstanding with any other ID fields that are found in other tables (when you decide you need them)

@GaP42 Thanks for the info on the reserved words and the ID (field) naming convention. It brings back memory's with VB6 Hungarian notation.

 

[Davebert]

I noticed when I reply to a post that has a link in it I get an error or warning about possible spam etc and if I removed the link from the quoted text I can get it to post. Is that because I am new and am moderated until I get over a certain number of posts?? And one of my replies is missing although June7 was able to quote it.
Thanks

 

[MajP]

Here some guidance from AI when naming fields. Not bad list
. One difference for me, and it is helpful for new users is to name the foreign key different from the Primary key. In my order table the CustomerID would be CustomerID_FK. Then you clearly know it is foreign key and not conflicts when including both in a join.

When naming fields in Microsoft Access, prioritize clarity and consistency by using descriptive names that accurately reflect the data type and purpose of the field, avoiding spaces (use underscores instead), and following a consistent naming convention (like using prefixes for specific data categories) to make your database easier to understand and maintain.

Key guidelines for naming Access fields:

• Descriptive names:
  Use full words or phrases that clearly indicate what information the field stores, like "CustomerName" instead of "Name" or "CustID" for customer identification number.
  
  
• No spaces:
  While technically allowed, avoid using spaces in field names and instead use underscores to separate words for better compatibility with other database systems.
  
  
• Consistent prefixes:
  Consider using prefixes to categorize fields, like "tbl_" for table names, "fk_" for foreign key fields, or "dt_" for date fields.
  
  
• Avoid special characters:
  Refrain from using special characters like punctuation marks (except underscores) in field names.
  
  
• Match data type:
  If possible, incorporate the data type into the name, like "PhoneNumber" for a phone number field.
  
  
• Be concise:
  While descriptive, try to keep names relatively short to prevent clutter.
  

Example naming conventions:

• Customer table:
  • CustomerID (Primary key)
  • FirstName
  • LastName
  • EmailAddress
  • BillingAddress_Street
  • BillingAddress_City
  • OrderID (Primary key)
  • CustomerID (Foreign key referencing Customer table)
  • OrderDate
  • ShippingAddress_Zipcode
  • TotalPrice

• Order table:
  • OrderID (Primary key)
  • CustomerID (Foreign key referencing Customer table)
  • OrderDate
  • ShippingAddress_Zipcode
  • TotalPrice

I

 

[June7]

All mostly good points.
Except phone number is not a number (as in a quantity), it is an identifier, same with SSN and ZIP. Have to decide whether or not to save symbols with digits. Phone numbers probably never have leading zeros so saving its digits in a number type field probably ok but can't old SSNs have leading zeros, ZIP certainly can. And foreign post codes even have letters.

 

[Pat Hartman]

No it doesn't make sense. 1 table makes sense. With what you have given us, I see only 1 table, exactly as it is. Again, you only need one table for this data, exactly as you have it now.

In order to make data entry easy and ensure consistency, I would create three additional tables for Color, Type, and Brand. These would be "lookup tables" and be used to populate the RowSource of the three combos. Of course you could hard-code these as value lists but that becomes maintenance intensive and for anything more than a few, never changing entries, I would use tables rather than hard-coded value lists.

 

[Davebert]

Here some guidance from AI when naming fields. Not bad list
. One difference for me, and it is helpful for new users is to name the foreign key different from the Primary key. In my order table the CustomerID would be CustomerID_FK. Then you clearly know it is foreign key and not conflicts when including both in a join.

Good info. Thanks for posting this. I will switch to this from now on as it is not hard to make the change. Are underscores safe to use? It looks like it according to your list.

 

[Davebert]

In order to make data entry easy and ensure consistency, I would create three additional tables for Color, Type, and Brand. These would be "lookup tables" and be used to populate the RowSource of the three combos. Of course you could hard-code these as value lists but that becomes maintenance intensive and for anything more than a few, never changing entries, I would use tables rather than hard-coded value lists.

Thanks for this extra add info similar to June7's recommendations. This helps me a lot as I was having trouble figuring out how to split up the data columns into separate tables. Can the reserved word "TYPE" be used if I add an underscore and some other characters? In other words is it only a reserved word when used by itself or even when mixed in with some other letters? It's bedtime over here so I will be back tomorrow, Thanks to all in this thread helping out The best source I found online as far as a help group goes by far.