Cascading combo help

[kernowboy]

Hi,

I wondered if someone might be able to help me please. I'm trying to create a database for my construction firm. This is the general structure of it:-

I have the following tables set up:-

tbljob with the fields:-
JobID (primary key)
Job Name
Job Address
Client Name
Client Address
Client Phone
Client Mobile
Client Email

tblcostitem with the fields:-
CostItemID (primary key)
Job Name
Item Name

tbldiary with the fields:-
Date
Job Name --> lookup to tbljob
Cost Item --> lookup to tblcostitem
StaffID --> lookup to tblstaff
Hours

tblstafff with the fields:-
StaffID (primary key)
Worker
Cost Rate
Charge Rate

The idea of it is that I want to record a diary of what worker has worked on what job, and doing what item of that job for how many hours. I can then produce a report that can show all data, or be grouped into jobs and cost items, showing how much each has cost, and what can be charged.

The bit I am stuck on is this. I have created a lookup so when you fill out the diary a combo box comes up with the list of jobs. What I then need to happen is to be able to select the cost items relevant to that job, as each job may have a completely different list of job items. I also need to be able to add to this list of items at any time, and add new jobs to the database.

I believe what I'm trying to do is a cascading combo box but I'm struggling after googling it to find my solution. I am a relative beginner to access.

I will get all this working as tables and then create forms as a user friendly way for inputting the data. I have attached a rough copy of the database so you can see what I'm talking.

Hope this makes sense.

Many thanks for any help.

Ali

 

[John Big Booty]

Welcome to the Forum.

Given that you have your Table structure. You will now need to work on your cascading combo boxes at form level. Your Tables are only for storing data, nothing more nothing less. Your users should have no direct access to the tables or the data stored therein, all interactions with said data should occur through the "Filter" of a form, either directly or via a query.

 

[kernowboy]

Hi,

Thanks for your reply. I have taken that advice on board and started creating my forms (only rough layouts at present). I have tried sorting out the combo boxes using one of the methods I found on this forum but I can't seem to get it to work. Have attached my latest file. Can anyone shed some light on this please.

Many thanks

Ali

 

[John Big Booty]

OK, I think the first thing you really need to do is to properly normalise your Table structure, something along the lines of;

tbljob with the fields:-
JobID (primary key)
Job Name
Job Address
Client Name
Client Address
Client Phone
Client Mobile
Client Email

tblcostitem with the fields:-
CostItemID (primary key)
JobID (foreign key)
Item Name

tbldiary with the fields:-
Date
JobID (foreign key)
CostItemID (foreign key)
StaffID (foreign key)
Hours

tblstafff with the fields:-
StaffID (primary key)
Worker
Cost Rate
Charge Rate

This will make this, and other, processes a lot easier.

 

[kernowboy]

Thanks guys for all your help, I've totally re-structured the database now on your recommendations, now also added sub forms after reading info on this site, and now also got the cascading combo working using queries. Great site, I'm enjoying creating my database and finding this site an excellent read. Sure there will be a few more questions to come.

Many thanks

Ali