Query multiple tables 1

[chunk22]

I have a main table which user enter generic details regarding an incident, i.e. Name, location, person involved, and Incident Type (This will then envoke what form is opened next).

Incident types for example are Crime, Anti-Social Behaviour, Violence, etc. These incident type all have their own table, both main table and for example the cRime table have Serial No. which originates from the Main one.

The two forms are bound by Serial No.

On form two you have to enter what retailer was involved.

What I want to be able to do is pull a report by retailer and incident types, in a given period so for example - All crime incidents @ HMV between 10/02/05 & 10/03/05

To be enable this search I want the user to select the relevant detail from a seperate form which contains drop down lists for the criteria.

Can anyone help

 

[M8KWR]

quite easy, create a form with the relevant drop down, and have a date range, so start_Date and End_Date

Create yourself a query that use parameter value, but links directly to the form.

so for example, if you are in the design view of the query in the criteria for the date field with the query you would put the following in

>=[forms]![NAME_OF_FORM]![START_DATE] and <= [forms]![NAME_OF_FORM]![END_DATE]

then in the Incident types criteria you would use something like

like [forms]![NAME_OF_FORM]![Incident_types_FIELDNAME]

I hope this help, if you give me the name you call your combo boxes/text boxes within the form then i can write the query for you, but i hope i have pointed you in the correct direction.

If you have any issue please let me know.

 

[chunk22]

Thanks for the reply, I have done something similar like this so understand where you are coming from however.

The issue I have is that the second table I want to query is dynamic and depends on what Incident type is selected from the main form as to what table holds the information.

Example:

Main_frm.[Incident Type] = Crime

Form to be opened on button click is Crime_frm (Crime_tbl)

How do I query a dynamic table?

Many thanks

Chunk22