Need help with table set up

[Cassidy]

Hello! I am building a database for an environmental remediation company. It will include a list of all the equipment and materials they own, all the equipment and materials they rent/buy, and a list of vendors. This is an ongoing project and this isn't my sole responsibility, so it will be months before this is where I want it to be. As of right now, I am having difficulty on deciding how to split my information. I haven't added any of the vendor list yet, there are over 1000 vendors that need to be added. I am working on the own/rent equipment lists. As of right now, I have these two tables with the following fields:

Table: OwnedEquipment
ID
Make
ModelYear
Description
Type
LocationCurrent
LocationNeeded
Quantity
DateNeeded
Notes

Table: RentedEquipment
ID
Make
ModelYear
Description
Type (This distinguishes what each item is, such as heavy equipment, materials, power tools, etc. both tables have the same types)
LocationCurrent
LocationNeeded
Quantity
DateNeeded
Notes

I thought that separating the equipment by owned/rented was the best, but now I am having issues creating the appropriate queries/reports. For example, if I would like to print a report of all the heavy equipment between both tables, the only way I can currently do it is to have two separate columns in the same query, but it produces a lot of duplicate values.

I am starting to think it would be best to have a different table for each type, and then include a yes/no checkbox to distinguish between rented and owned. For example, this would be the setup I am thinking about. All of the tables would have the same fields.

Table: Heavy Equipment
Table: Materials
Table: Site Support
Table: Hand Tools
Table: Power Tools

Fields:
Make
ModelYear
Description
Quantity
Notes

Then I am thinking about having a seperate query for each job that would come up to cover the location/date that items are needed. for example:

Query: NewYorkCity
Description
Type(this would be the same as the table heading, such as heavy equipment, hand tools, power tools, etc)
Quantity
DateNeeded
Notes

I don't know exactly how to do what I am describing, but my end goal(again, months down the road) is to be able to create standard reports for each job that may come up, and I will add individual items from each table to the job report, based on what we need. For example, we may have a job in New York City, and we need 2 excavators, clean fill, a chainsaw, and the job file. The tables that information would come from would be the heavy equipment, materials, power tools, and site support tables, respectively. If anyone could offer any assistance on table setup to organize the data and relationships, I would really appreciate it!

Thanks in advance.

 

[plog]

You're going in the wrong direction. You shouldn't have tables with similar structure. That means not only shouldn't you have a table for each Equipment Type, you shouldn't even have different tables for Ownership types.

Owned and rented equipment should all be in the same table. The way you differentiate them is by adding a new field to identify if they are rented or owned.

Onto another issue: do you want to keep a history of where these items have been? You are tracking current location, but do you want to know where something was 1 year ago?

 

[spikepl]

All of the tables would have the same fields.

So all your stuff goes into one table. You have already experienced what a pain it is when similar things are in different tables becasue qeueries are a pain to construct. Splitting it further will just cause more PITA!

Google and assimilate database normalization

 

[Cassidy]

Ok, so should I list all equipment in one table and distinguish between owned/rented with a yes/no check box? I think that would be the simplest way but I'm not sure if that is the best way.

The company I work for isn't a huge company, so there isn't a large number of items, most jobs tend to require the same items. So to answer your question, I don't need a history of where each item has been. This company owns some heavy equipment(such as skid steers, bulldozers, excavators, etc), but it's only about 10-15 items. Same with most of the individual "types" I discusses earlier, There are about 20 different power tools. The materials list is by far the largest list.

I thought I had way too much overlap between tables, but I wasn't sure if I should be breaking them down into smaller tables. Is it possible to do what I said earlier, to be able to create a printable report for each job and select individual items to add to the list? I know I could create a query and enter criteria to only list the items I want, then just make a report of the query, but I wasn't sure if there was an easier way.

 

[gemma-the-husky]

Ok, so should I list all equipment in one table and distinguish between owned/rented with a yes/no check box? I think that would be the simplest way but I'm not sure if that is the best way.

The company I work for isn't a huge company, so there isn't a large number of items, most jobs tend to require the same items. So to answer your question, I don't need a history of where each item has been. This company owns some heavy equipment(such as skid steers, bulldozers, excavators, etc), but it's only about 10-15 items. Same with most of the individual "types" I discusses earlier, There are about 20 different power tools. The materials list is by far the largest list.

I thought I had way too much overlap between tables, but I wasn't sure if I should be breaking them down into smaller tables. Is it possible to do what I said earlier, to be able to create a printable report for each job and select individual items to add to the list? I know I could create a query and enter criteria to only list the items I want, then just make a report of the query, but I wasn't sure if there was an easier way.

bits in bold.
definitely both the easiest and the best.
if you decide you need this later, it will be easy to add it
think in terms of the data structure

what you need is a table of materials, a table of jobs, and a tables of job-materials

you then have a form that lets you pick a job, and a subform that lets you add materials used to the job.

reporting is a separate task, but basically you will use this same data to generate a formatted report. A "printed" form is generally not as good as a "printed" report, so keep them separate, really.