6 Tables, one form. I give up.

[klufkee]

Hello all,

I have been handed a project that is no fun. They gave me a DB with 6
tables. They relate to one another but not by a common field.

Example:

Table_1 Table_2 Table_3 Table_4 Table_5 Table_6

field_1 field_3 field_4--field_2 field_5 field_1

field_2--field_2 field_5 field_3 field_4--field_2

field_3 field_1---field_1 field_4 field_3 field_3

field_4 field_4 field_2 field_5 field_2 field_4

field_5 field_5 field_3 field_1---field_1 field_5

(Sorry for the nasty graphic but I'm no good at ASCII art)

This is not an exact representation of the relationships but I'm just
trying to illustrate that field_4 in table_3 has data that matches the
values in field_2 of table_4. Also it may be helpful to note that none
of the tables have the same amount of records. e.g. table_1 has 35175
records, table_2 has 34666, table_3 has 6662 and so on.

All I want to do, (and have been trying to do for 2 weeks), is be able
to have a user search through the data. I would like to have a form
that allows them to select or enter a value from a drop down or combo
box and have the results filtered to only show the records that match
that value in that particular field. If it is helpful, I have found
that when the data from all the tables is combined (according to the
relationships), there are 22057 unique records. Of course some of the
data is duplicated when viewed this way because value 'X' in field_3 of
table_1 matches 4000 records from table_4. I would like the user to be
able to select value 'X' and have the results filtered to just those
4000 records that match. Then hopefully be able to select a value from
a different field to further filter the results.

Like I said, this is something that I have been trying to do for 2
weeks now with not much luck. I was able to create one form that would
display all the results (22057 of them) but it would not let me chose a
value from a combo box. (They would all show up in the box but when
clicking on the nothing happened.) This whole thing has me mystified. I
even bought the 'Inside Out' book from MS Press! If anyone out there
has some insight into what I'm doing wrong please let me know.

Thanks to all and sorry for the really long post.

Bill

 

[Ron2006]

Is the final result to be a subform showing only those records that
match ALL of the criteria and ONLY after they have selected all of the
criteria?

 

[klufkee]

A subform would be great. (Anything would be great if it works.)
But yes, I think the best end result would be to have the user select
whatever criteria they want, then hit a button to display the results.
(Possibly in a subform if that's the easiest way to do it.)
The whole systems will pretty much be used to look up old records and
find the .pdf and .doc files that are linked to that record.

Thanks,
Bill

 

[Ron2006]

Then here is a possible solution, that may give you some ideas - all
based on what little I understand of your system and their
relationships.

Six combo boxes or as many as needed to successively narrow down the
selection. I am going to give you a dumb example but it might give you
some ideas.

box1 - query that selects "distinct" (or unigue) Countries.

box2 - query that shows states or provices using a table that has
criteria of matching the country from Box 1

box 3 - query that give you counties produced from table that could
have criteria of both boxes 1 and 2 or just 2 depending on structure

box 4 - based on query that gives you cities using criteria from box 3
andor 2 andor 1

box 5 - based on query that gives you alphabetic street names with
criteria supplied by box 4 etc.

button to show final form that has a query with criteria from all 5 of
the boxes or whatever is necessary.

Basically each box has a query that is refining the final search
criteria, and it does not matter where it is coming from- simply that
it is constrained by sufficient criteria from the previous boxes to
result in what you want.

IMPORTANT....................

Part of the change event for each box is to requery the next box in
line that is dependent on itself for criteria and constrict each box to
only those items in the list.

Hope this helps
Ron.