Quick Theory Question

[NewShoes]

Hey all,

Just wondering if I am currently doing the correct thing.

I have a problem (well, it might not be a problem) and have tried to explain below...

1) I have an Employee Table which has a Manager field. I have another table called Managers which is made up of ManagerID, ManagersName and Enddate.

2) I have a query that selects ManagerID and ManagersName if the enddate is null (i.e. hasn't been ended).

3) In the main Employee table, I use a lookup/combo box on the datasheet view to use the query mentioned above. I can then select a name of a manager.

Now, the problem is that "behind" this managers name is the manager ID (I have it bound to ManagerID). So, when I come to build certain queries, it sometimes doesn't work becuase I'll be querying for "John Doe" but the database stores it as "1", as thats the managerID for John Doe.

Is this correct? I'm having some trouble with queries becuase of this but thought that this was the correct way to do things and have a normalized database!

Thanks,
-NS

 

[SOS]

You do NOT use the lookups directly in the tables. Use them at FORM level. Read this for more info.

 

[NewShoes]

You do NOT use the lookups directly in the tables. Use them at FORM level. Read this for more info.

I see, I've read the info you linked to. Could you shed any light on what you mean by at form level?

Thanks,
-NS

 

[SOS]

You have the lookup tables still but you use a combo box (or list box) which has the lookup table as its row source (a query using that is better so you can set a sort order). Then the combo (or listbox) is bound to the field within the table your form has as a recordsource. It should be storing the ID and not the text. You can then, when you need to run a query on the data, include the lookup table in the query and with the PK/FK linked you can provide the text of the item and not the ID.

See this and see if it helps you understand using a query in a report that has the ID"s stored.

 

[NewShoes]

Thanks SOS. I've had a quick go and it seems to be the way to go! It's very different to using Lookup fields in the table and seems a littler trickier at the min. I'll have to get my head around it and see what I can come up with

Thanks again for your help.
-NS

 

[NewShoes]

One more quick question...

When creating a combo box with the wizard, it asks you if you like to store a value in a field or if Access should remember this value for later. Can anyone explain what this means? I have no idea!

Thanks,
-NS

 

[SOS]

One more quick question...

When creating a combo box with the wizard, it asks you if you like to store a value in a field or if Access should remember this value for later. Can anyone explain what this means? I have no idea!

Thanks,
-NS

Remember the value for later just means that it is an unbound combo and selecting an item in it will set the value of the combo to that selected item. Store a value in a field means that it will be bound to a field in the form's underlying recordset so you can store the selected value in the table. That is the one you are looking to use.