Table Structure / Setup (1 Viewer)

[ShaneX]

Hello All,

New to access so sorry in advance hopefully i explained my thoughts adequately.

ID | JOB_NAME | JOB_DATE | TYPE | SIZE | etc...
1
2

Currently, each job has the information entered here, with just basic information about the job entered.
Then there is another form which has the various bids of us, and other companies, entered for each of these jobs in question..

ID | JOB_NAME | OUR_BID | COMPANY_1 | BID_1 | COMPANY_2 | BID_2 | etc..
1
2

Obviously this database doesn't work, as its just static, therefore I'm trying to link the companies to their bids so that I can create an additional sheet breaking out the companies with their individual bids, average bid price etc. Unfortunately, there is too many other companies on each job, and they change frequently, therefore having a field for each company wouldn't be feasible.

COMPANY | JOB TYPE | BID | AVERAGE_BID etc..


I can't exactly figure out a way in which I could do this, but I'm sure its a lot simpler then I'm making it. Any hints or tips would be greatly appreciated.

 

[plog]

It's different than you're making it. Databases aren't spreadsheets--you need a whole different mindset when setting up your tables. That process is called normalization:

Database normalization - Wikipedia

en.wikipedia.org

Give it a read, practice a few tutorials then apply what you learn to your database. A big thing I see with your data is that you have numerated fields and "sets" of field (e.g. COMPANY_1, BID_1, COMPANY_2, etc.). When you feel the need to do that, its time for a new table. All those Company/Bid values need to go into a new table, structured like this:

tblBids
bid_ID, autonumber, primary key
ID_Job, number, foreign key to Job table (first table you listed)
bid_Company, text?, will hold whatever value is currently in COMPANY_X fields
bid_Amount, number?, will hold whatever value is currently in BID_X fields

That's it, those 4 fields can now accomodate an unlimited number of companies/bids per job. You don't accomodate data horizontally (by adding more columns) in a database but vertically (by adding more rows). Whenever you get a new bid, you simply add a record to tblBids.

Again, read up on normalization, do a few tutorials, give it a shot on your data, then complete the Relationship Tool in Access, take a screenshot and post it back here and we can help get this right.

 

[ShaneX]

I'm actually trying to move this data over from a spreadsheet.. so that would make perfect sense haha.
Ill give that a read and see what I can accomplish.

Thanks

 

[jdraw]

You will experience and learn a lot of database tables and relationships by working through this tutorial from RogersAccessLibrary. What you learn can be used with any database. Spend 30-45 minutes working through the tutorial. May be the best use of 45 minutes. You will learn.
Good luck.

 

[ShaneX]

Thanks for the advice !

after working through this tutorial, I came up with this result.. I'm least confident about the SQFT bring in the jobs table, but it would be unique per job, but then I end up having two foreign keys related to the SQFT/Bid table. I feel a lot more on the right track though.

 

[plog]

First, only use alphanumeric characters in table/field names--that means no slash in tblSQFT/BID. Then, while those may be the right tables, the relationships are wrong. There should only be 1 way to trace a path between any tables--yours creates a loop--one of those relationships needs to go. Its your data, so only really you know that, but we can help get you there. Please explain the role tblSQFT/BID plays in this--what real world entity does it represent?

 

[jdraw]

We need some more info on your requirements. Tell us more about a Job and Bids. Where does SQFT fit?
Can you tell us about a specific Job in more detail?

 

[ShaneX]

sure!

Basically a job will have multiple bids from different companies, these bids are based on the square footage of the project, which is why i have the SQFT related to the job. I then am taking this collection of bids and entering it into the database, per job.

Then I'm just trying to relate the square footage of the project to a companies actual bid, to find out what other companies are usually bidding per square foot for different types of projects.

The chain would be Project given -> Bids submitted per sqft -> analyze data of bids

Sorry!

 

[jdraw]

Basically a job will have multiple bids from different companies, these bids are based on the square footage of the project,

Please be clear:
What exactly is a Project?
What is a Job?
What is a Bid?
Where do Companies fit in your business process(es)?

Can you provide some data for each of these entities?

 

[Pat Hartman]

If SQFT means what I think it does, it would be an attribute of the job and not the PK of the junction table.