Form using relational data

  • Thread starter Thread starter Paula
  • Start date Start date
P

Paula

I have two tables, one for subcontractors the other for
trades. I want to be able to select the trade in the
form and have it fill in with the proper payrates etc.
Problem 1: In trying to relate these tables I get and
arrow instead of the "1" and infinity sign. I have tried
all three options in the join area. All it does is
change the direction of the arrow. I don't know what
else to try. Which may be why I have the following other
problems
Problem 2: I have a combo box for the trade. I can
see all the trades but only the first row comes over to
my form and I can't change it to any other trade.
Problem 3: Now my subcontractors are duplicating. I
am just setting this database up so I don't have a lot of
records so far. I only have two records in the
Subcontractor table and 5 records in the Trades table.
My form went from having two records to showing ten
records. My switch every other record but with the same
trade.
 
Paula, I'm guessing that:
- one contractor can do more than one trade, and
- one trade can be done by more than one contractor.
That means you have a many-to-many relation between contractors and trades.

To resolve that you need a 3rd table, with fields like this:
- ContractorID Number (Long) foreign key to Contractor.ContractorID
- TradeID Number (Long) foreign key to Trade.TradeID
- PayRate Currency Hourly rate this contractor charges
when performing this kind of work.
Name it (say) ContractorTrade.

Once you have set up those tables and relationships (Tools | Relationships),
the interface will look like this:
a) Main form bound to Contractor table;
b) Subform bound to ContractorTrade.
The subform will have a combo box where you can select the trade the
contractor does, one per row, for as many rows as you need.
 
Yes, those guesses are correct. They can also be on one
or more jobs at the same time. I am still not clear on
how to set up the subform and whether I may need more
than one subform given the info below. This is what I
have now.

Table #1-Trades (Text-unique 6 characters-foreign key to
Database Table.
Table #2-Subcontractors (Text-unique 6 characters-foreign
key to Database Table.
Table #3-Jobs (Text-unique 5 characters-foreign key to
Database Table.
Table #4-Database Table (auto number-long integer)

Tables 1,2&3 all relate to the Database Table in a one to
many relationship. If I understand you correctly I need
to have the Subcontractors and Trades relate to each
other and then to the Database Table. What I am confused
about is what fields do I have on what table? This is
what I would like to accomplish on my form:
Subcontractor - combo box that would select
subcontractor and populate address, phone, fax, contact
fields.
Job - combo box that would select job and populate Job
Name, County
Trade - combo box that would select trade and populate
straight time, fringe benefit fields, OT, Holidays
Sub wages - fields that would match the Trade pay
fields so I can compare what the sub is paying to what
the trade says it should pay.
Can I do this?
 
Hi Paula

I feel quite unclear about what data you are trying to store. I don't
understand the purpose of the database table.

I would imagine you need at least:
- Clients table: one record for each person/company you do work for.

- Worker table: one record for each employee or subcontractor.

- Job table: one record for each contract/job you take on.
Fields like owner, location, Start date, promised finished date.

- JobDetail table: one record for each aspect of a job. Fields like:
JobID (foreign key: which job this was part of)
JobDetailDate (when done)
WorkerID (foreign key: who did this aspect of the job)
Hours (number of hours by this worker on this date for this job)
PayHate (dollars per hour)

The JobDetail table stores only the JobID, not the location, owner etc.
Those are derived from the Job table. Likewise, the JobDetail stores only
the WorkerID, not the worker's name, etc.

In the end, that is not so different from the Northwind sample database.
Open it in design view, and look at the Relationshps window (Tools menu).
Particularly, compare the Customers, Orders and Order Details.

My previous reply does not relate to performing jobs, but to a database that
needs to store the capabilities of each employee/subcontractor, and match
their skills against the skills required for a job.
 
Back
Top