I need help w Best Practice for a single entry query...

[Dave]

that will look across a layered or tiered table or something to this effect.

This is an example of a table that I am thinking about building. But before
I do, I don't know if it will give me the result I want.

I would like to make a single entry using an identifier from Col1, 2 or 3
and get a return for the employees that would fall in that line item.

Example if the identifier from Region 1 is input, all the employees in 1, 2,
3 and 4 would be returned.

and if the identifier for Local 2 is input only employees 2 would be
returned.

I know I can accomplish this with 2 columns and keep repeating the employee
data, but administratively, that would be a pain.

If I have not confused you, is this possible or is there an easier approach.

I only want to use a single entry point in a form.

Thanks.

Col1 Col2 Col3 Employees
Region1 # District1 # Local1 # Employees1
Region1 # District1 # Local2 # Employees2
Region1 # District2 # Local3 # Employees3
Region1 # District3 # Local4 # Employees4

Region2 # District4 # Local5 # Employees5
Region2 # District5 # Local6 # Employees6
Region2 # District6 # Local7 # Employees7
Region2 # District6 # Local8 # Employees8

 

[John Ortt]

I would probrably use a union query to create the data and then repeat it
with the region data.
If you were'nt dealing with a huge amount of records this would be fairly
effective...Something along the lines of:

Select [Name], [Town], [ExtNo]

from Datatable

Union Select [Name], [Region], [ExtNo]

from Datatable;

This will give you duplicate entries but if you then link the result to the
text in your textbox it should do the trick.

Failing that you could just have two lines in your criteria expression, one
underneath the Town Column saying "like forms.dataform.Location" and the
same on the next criteria line down but under the region column.....

On second thoughts I think I would use that approach....

Hope that isn't too confusing but I thought I would leave both options on
for you....

John