Setting up a database

[yorky]

Hello, I have basic knowledge of Access and know that if it is set up correctly it can be used to store records and create reports at the click of the mouse (once the report is created). However, I am having issues with duplicate records when creating a query or report using multiple tables with a field in each table containing the same information (in my case truck number). When I create a query with just one table there are only 50 records then when I add another table linking the field i get 300+ records and in the other table there are only 20 or so. What could I be doing wrong? My knowledge is more on the use and ability of Access and not the setup from scratch.

 

[plog]

For lack of a better term, you have "duplicates" in a table you aren't expecting them in.

When you JOIN tables every record in one table matches with every record in the other table based on how you tell them to match. Suppose you have these 2 tables:

Table1
Field1
A
B
C
C
C
D


Table2
Field2
B
B
C
C
D


If you JOIN them from Field1 to Field2 the resulting rows is a product of how many records you have in each table.

A = 1 row * 0 rows = 0 rows
B = 1 row * 2 rows = 2 rows
C = 3 rows * 2 rows = 6 rows
D = 1 row * 1 rows = 1 row

You are getting multiple values in your query because that is what is occuring when you JOIN your tables.

 

[yorky]

Is there a way to avoid this or would it be better to create one table with every possible field, create and use multiple forms to enter the information and then create queries and reports from there?

 

[plog]

Data normalization is data normalization--generally there's a right way to do it for every data set. Sounds like either haven't set up your tables properly or you are trying to JOIN data that shouldn't be joined.

Can you post a screenshot of your Relationship tool so I can see all your tables/fields?

 

[yorky]

View attachment screenshot.xlsx


wouldn't let me paste so I did it in excel 16, word was to big a file

 

[plog]

I can't see all your fields, but from what is shown I see these issues:

Bad names: You should only use alphanumeric characters as names--no special characters and no spaces. This just makes coding and querying harder later on. Remove all those spaces.

Bad names 2: [Date] is a reserved word in Access (https://support.office.com/en-us/ar...-symbols-e33eb3a9-8baa-4335-9f57-da237c63eabe) and shouldn't be used as a name. Don't use any of these words as names.

Bad names 3. Too generic names. Even if Date wasn't bad because of the reserved word thing, I have no idea what Date it represents. Prefix all generic names with what they are for (MaintenanceDate, ExpenseDate, SettlementID, etc.)

Improper use of foreign keys (https://www.w3schools.com/sql/sql_foreignkey.asp): Every table you list has an ID field, but when you JOIN your tables you are using the Truck field, that is wrong. You should have an autonumber primary key in your table (which I am sure ID is) and then you put it in external tables to JOIN them. That means Truck comes out of Payroll Test, Truck Maintenance, Selltement Payment and they instead get the Setlltement Expense ID field in them.

Storing Calculated values. You don't store more data than necessary in a table--that means you don't store balances you can calculate, people's ages if you have a birthdate and you don't store information about a date if you have a date. I'm guessing Week Ending and Month Of relate to Date in Settlement Expenses--if so, they need to go, just use the value in Date to determine those other values, don't store it.

That's all I can see from what you have given me, if you could expand your tables I could dissect more.

For now, can you give me 2 paragraphs:
#1 tell me what your organization does. Preten its career day at an elementary school and you telling the kids what it is you do. No database jargon, just simple terms without regard for the database at all.

#2 tell me what this database is to do. Try to lay off database jargon, just give me a high level view of the real world process you are trying to capture with it.

 

[yorky]

the database if for a small owner operator trucking business with 3 trucks. trying to use access so that record keeping (trips, mileage, fuel, other expenses payroll etc.) is not only easier but in one place/file. It would/should also be easier to generate reports and balance sheets. currently using multiple excel files to keep the data.

 

[plog]

I would read up on normalization and complete your Relationship tool. Looking at it closer I see 8 tables in your database, but only 3 in your Relationship tool. Fully create your relationship tool, expand your tables so I can see all fields and then post a new screenshot.