Query on form

[freemansurv]

Hi,


I've used Access before but not for many years, so I'm not a complete novice (but pretty close). I'm slowly attemptinb to build a simple client database that uses two tables. One for client details (name, address etc), the second table for Jobs (I'm a surveyor, so a job would be described with fields such as location, cost, status etc). On my client form (used to view and add new clients to the client table) I want to have list box that shows any jobs that are associated with the client that is currently shown on the form. I link jobs to clients simply by client name (not brilliant, but sufficient for my needs).
I've tried writing various queries etc but I can't figure out how to link a query that ties a client to a job, to be listed in the list box on the client input form. Some clients are linked to lots of jobs, others only to one job.
Any help would be super appreciated. I'm starting to feel old and bit thick due to my lack of progress (I've been trying to get it to work for days!).
My PC is running windows 10, I'm using using Access 2016.

Cheers, Adam.

[Micron]

You should be using a main form (clients) and a subform (jobs) linking client form (client name?) to job list (client name). It is easy to add job records to a client this way - not so much to a list box. You could use this main form to add clients as well.
I would not link jobs to clients by name. If client name changes (e.g. because of a business name change, marriage, whatever) you will have a problem. The time to do things right is now - it takes less time than it takes to fix it, and you end up with keeping more of your hair.

[kd2017]

Maybe something like this attached example

[freemansurv]

Gosh, thank you so much! Can I send you my database?

[freemansurv]

Thanks very much - I will look into this. Yes, I would ideally like to link the tables with a client number

[Micron]

Gosh, thank you so much! Can I send you my database?

No idea to whom you're addressing that to.
Typically you would copy/compact/zip and then post the copy here. Look at the forum header for the link "How to attach files" if you need help with that.

[freemansurv]

No idea to whom you're addressing that to.
Typically you would copy/compact/zip and then post the copy here. Look at the forum header for the link "How to attach files" if you need help with that.

Ah ok, noted - I actually just got the thing to work by using a sub form

[Micron]

Great! Now if you would mark it as solved, it will help those who try to help - they can skip over the thread if they're looking for a problem to solve.