Help with simple design

[June7]

Yes, underscores are okay (see my examples in post 16), although I minimize use because of the reach to number row and MajP's reason.

And yes, if you add characters to TYPE the name is no longer a reserved word (again, see post 16).

Review (more from Allen Browne) http://allenbrowne.com/AppIssueBadWord.html

Some reserved words as field names won't be an issue but better to avoid than to find out later when something breaks.

 

[Gasman]

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

Yes.

 

[The_Doc_Man]

Hello and welcome to the forums.

In response to your plea for help, I give this advice: This is a database forum so here, you can search for "Normalization." If you want to learn using multiple viewpoints from the web, you must search for "Database Normalization" - because by itself, "normalization" is used in at least half-a-dozen disciplines including math, chemistry, pharmacology, and international diplomacy. When you find articles, start from those coming from a .EDU domain and definitely look for several. Not that you are looking for technical differences, but you need to find articles with which you can connect. Some articles get a bit abstract. Once you think you see the light, look at a few of the .COM domain articles. The reason I suggest looking at the .COM sites 2nd is that they often have something to sell you and initially, you don't need the distraction.

Normalization is a way for you to put your database together such that the various relationships work correctly. Also, it's a way to minimize the size of a database by not having to repeat something over and over again. Instead, you just point to the details - and the pointer is almost certainly shorter than the detailed description of whatever was being pointed to.

 

[GPGeorge]

Hi, I am still trying to figure out the most important part of relational database design. Now I am much older and I thought wiser but am still stumped when it come to making relationships between tables. I started with MS Access a long time ago and had some of those 700 page books and ended up with a flat file single table database in the end as all that foreign key and 1 to1 , 1 to many, and many to 1 stuff didn't sink in. So now I am back with another go at it. I have put together a single table flat file db using LibreOffice Base and want to split it into multiple tables and make it relational. I have split it up and don't know which field should be in table and table two. I have ordered an older version of MS Access (2010) that will be here soon. I hope somebody can get me over to the dark side. I am in Florida.

One thing you can do to get a kick-start would be to upload a copy of the current accdb you have and solicit ideas on how to Normalize it. Doing so gives people a concrete basis on which to make suggestions and offer explanations.

 

[Davebert]

I avoid underscores for two reasons. I find them jarring when reading and annoying when typing (too lazy to use the shift key). I prefer CamelCase and reserve the underscore for when I want to emphasize the separation. As long as I use upper case when I define a variable name, Access shifts for me even when I type in all lower case.

The simplest way to avoid reserved words is when you are naming things with short, simple, names that may conflict like "type", qualify your name with a modifier like ProdType, MachType, etc.

Good info. Thanks. I have another question before I get started today to anybody that cares to answer it. When I was using LibreOffice Base I was able to copy my table into the LibreOffice Calc (spreadsheet) and then create new tables by grabbing a column of data and importing it back as a table in the LibreOffice Base program. Can I do the same with Access as far as importing the data and turn in into a table? Or is is better to start from scratch and re-type in all the data from my populated single table flat file?
Thanks

 

[June7]

SELECT DISTINCT Type INTO tblTypes FROM tblFilaments;

SELECT DISTINCT Color, TD, HEX INTO tblColors FROM tblFilaments;

SELECT DISTINCT Brand INTO tblBrands FROM tblFilaments;

Now go into each new table and define key. If you want to change to autonumber primary key, that will involve more work.

 

[MajP]

SELECT DISTINCT [Type] INTO tblTypes FROM tblFilament

If a field or table name is a possibly a Reserved word, has spaces, or has special characters (% # ..) you are better off surrounding with []. You can be conservative if you think it might be problematic. You can always use [] and in certain cases like expressions you have to.

 

[Mark_]

When I need to import from a spreadsheet I LINK the spreadsheet FIRST, then do a query to verify the data makes sense. I'd rather make sure I ONLY import what I need rather than having to go back and fix data after the import.

 

[Davebert]

If you have a table with the Type field populated, you can create the Type table by using a make-table query. Start with a select query that selects ONLY the Type column. Then you press the sigma button to group by, then choose the make table query and run it. Do the same for each column you need to make a lookup table to support.

Ok, I may be stuck now as I have created the whole filament table like it was originally was. Then I created the 3 lookup tables ColorValues, TypeValues, and BrandValues. I made these by creating a new database for each of those. All of the above were cut and copied from the LibreOffice spreadsheet and that worked well except for the empty columns I had to adjust the Fieldname. Here is a screenshot to see if I need to make any changes as I cannot see more the one table at a time in the relationships window. Also I can't figure out how to change the name Database1 although I did rename the other 3 ok.

 

[Davebert]

If you have a table with the Type field populated, you can create the Type table by using a make-table query. Start with a select query that selects ONLY the Type column. Then you press the sigma button to group by, then choose the make table query and run it. Do the same for each column you need to make a lookup table to support.

I couldn't open the files from ACCESS as they are type .ODB from LibreOffice and they use some proprietary database called HSQL. I managed with cut/paste from the Calc spreadsheet though.

 

[Davebert]

SELECT DISTINCT Type INTO tblTypes FROM tblFilaments;

SELECT DISTINCT Color, TD, HEX INTO tblColors FROM tblFilaments;

SELECT DISTINCT Brand INTO tblBrands FROM tblFilaments;

Now go into each new table and define key. If you want to change to autonumber primary key, that will involve more work.

Don't forget I am getting into uncharted territory now. Where do I access the command line for SQL statements?
Thanks

 

[Davebert]

SELECT DISTINCT [Type] INTO tblTypes FROM tblFilament

If a field or table name is a possibly a Reserved word, has spaces, or has special characters (% # ..) you are better off surrounding with []. You can be conservative if you think it might be problematic. You can always use [] and in certain cases like expressions you have to.

New territory. Could you let me know where I access the interface for this query stuff? Thanks!

 

[June7]

Query Builder is on ribbon Create tab.
It has two views: Design and SQL.

I am sure you can find a tutorial somewhere.

 

[Davebert]

Query Builder is on ribbon Create tab.
It has two views: Design and SQL.

I am sure you can find a tutorial somewhere.

Ok, thanks. I am finding a few things the hard way like it seems if you name the original files wrong you might get one chance to change the names. I will do some tutorial searching. Added: I just did a simple query and it was too easy. So if I want to do more advanced queries I assume there is just the SQL editor where I have to manually type in the appropriate SQL statement to get a more focused result. So if I wanted to see how many white filaments I have in PLA from a certain brand I could do that? I think you already answered this as I scroll up to see any messages I missed or didn't understand yet. I found it: SQL View.
Thanks again

 

[June7]

"files" or "fields"? Can change names anytime.

 

[Davebert]

"files" or "fields"? Can change names anytime.

When I made my first database in my new fresh install of Access it named it Database1. When I made the other 3 DB tables I was able to change their names after I closed them. But Database1 is still the same name.

 

[MajP]

To import your other tables into a single database.

 

[June7]

You can change the database Application Title in File > Options > Current Database.

You can change the file name in Windows File Explorer.

 

[Davebert]

You can change the database Application Title in File > Options > Current Database.

You can change the file name in Windows File Explorer.

I thought that might be the case. I had to hunt down the default location and it was in Documents.
Thanks

 

[mike60smart]

Ok, I may be stuck now as I have created the whole filament table like it was originally was. Then I created the 3 lookup tables ColorValues, TypeValues, and BrandValues. I made these by creating a new database for each of those. All of the above were cut and copied from the LibreOffice spreadsheet and that worked well except for the empty columns I had to adjust the Fieldname. Here is a screenshot to see if I need to make any changes as I cannot see more the one table at a time in the relationships window. Also I can't figure out how to change the name Database1 although I did rename the other 3 ok.

From your screenshot it looks like you have created a database for each table. All of your tables should be in one database.