Lookup Wizard to Grandparent Parent Record

[StevieJD]

I'm trying to teach myself how to create databases in Microsoft Access. Unfortunately I have no one else to ask so I'm hoping that some kind person on this site will be able to help me?



I have thought up a fictitious Cleaning company which providers cleaning services to many different business clients. Each client will have many different sites and each site will need two (or more) cleaners.

I am comfortable in creating a one to many (parent / child) relationship, using the Lookup Wizard, between Client and site, so that when I populate the site table, I can lookup the Client from a drop down list. Like wise, when I assign a cleaner to a site, I can assign a cleaner to a site but looking up the site from
cleaner table.

However, eventually what id like to be able to do is select a cleaner, then assign the cleaner to the site, by using the lookup dropdown list (which I can already do), and then select Client from another drop down list without leaving the cleaner table / form.

Conversely, in a form, when assigning cleaners to sites, I also like to be able to select a Client, then select all the Sites the client has , and THEN select a list of cleaners so I can assign two or more Cleaners to the site, again using lookup drop downs.

I believe this will require a Grandparent \ Parent \ Child relationship. Can I use the lookup wizard to accomplish this, and if so how?

Thanks in advance for your help.

[isladogs]

Use three cascading combo boxes where the combo sql for the second (sites) depends on the client value from the first combo.
Similarly the cleaner combo sql will depend on the site value from the second combo.

[CJ_London]

so that when I populate the site table, I can lookup the Client from a drop down list

this sounds like you are using lookups in your table design. Although this can be done it is not a good idea as it will lead to confusion. See these links http://access.mvps.org/access/lookupfields.htm, https://bytes.com/topic/access/answe...-lookup-fields. Lookups (combo or listbox) are fine in a form.

However, eventually what id like to be able to do is select a cleaner, then assign the cleaner to the site, by using the lookup dropdown list (which I can already do), and then select Client from another drop down list without leaving the cleaner table / form.

if you have assigned a site to a cleaner, why do you need to assign the client as well? that is already covered when you assigned sites to clients

also like to be able to select a Client, then select all the Sites the client has , and THEN select a list of cleaners so I can assign two or more Cleaners to the site, again using lookup drop downs.

Really think you need to think your design through in more detail - what if you have assigned a cleaner to 20 sites already and they don't have time for this one? what if cleaner2 is not available at the same time as cleaner1? What happens when a cleaner is ill or on holiday?

From your description you need a minimum of four tables

Clients
Sites
Cleaners
Cleaner/Site assignments

and you need to manage these using forms and subforms, not lookups in a table

[StevieJD]

Thank you ridders, I'll think about this.

[StevieJD]

Thanks for the links Ajax - very, very useful.

It looks like I've got some more thinking to do!

[isladogs]

Agree with Ajax about table structure. However my original advice may still be relevant.
Whilst you're rethinking your design, have a look at this example of cascading combo boxes http://www.fmsinc.com/MicrosoftAcces...cascading.html

[StevieJD]

Once again, thank you very, very much.

I've limited experience of Access VBA, but I can usually get Excel VBA to do what I want it to, so this will be a very interesting and useful challenge!

[CJ_London]

@Ridders - there are some problems with that example

a) only works on single forms
b) advice is wrong - no need to requery if you update a rowsource
c) however you do requery the rowsource if the rowsource is written like this

"SELECT PhylumID, PhylumName " & _
"FROM tblPhylum " & _
"WHERE KingdomID = [cboKingdomID] " & _
"ORDER BY PhylumName"

also there is a missing space in the example WHERE line. Bit sloppy for FMS

[isladogs]

@Ridders - there are some problems with that example

a) only works on single forms
b) advice is wrong - no need to requery if you update a rowsource
c) however you do requery the rowsource if the rowsource is written like this

"SELECT PhylumID, PhylumName " & _
"FROM tblPhylum " & _
"WHERE KingdomID = [cboKingdomID] " & _
"ORDER BY PhylumName"

also there is a missing space in the example WHERE line. Bit sloppy for FMS

Apart from all that, is it ok????

I have to admit I'd only looked at the FMS example without ever testing it.
Perhaps should have uploaded an example of my own!

[CJ_London]