Create search form which returns forms

[sailock]

Hello , i am new in access and in this forum .
I have create a db with 20 tables and for each table i have a form .
Every table contains 2 fields as primary keys , lnumber and stDate.
I have done this because there can be a lot of same lnumbers but they have different dates.


My problem now , is that i want to create a search form where i can type the lnumber in one box and the other will show me the stDates that this lnumber has.
After i choose the date i hit the search button and then it shows me a list (or whatever) with the forms that contain the search criteria.
Then i open any form i want and see-edit it .
Sorry for my english .
Thank you

[burrina]

You will need to include those tables in a query and use your form criteria for the lnumber.

HTH

[sailock]

can u give me some examples cause i am noob ?

[June7]

Why not one table? Combination of lnumber and stDate is unique. Otherwise, use a UNION query to combine the 20 tables into a single dataset (will look like the table you should have anyway).

[sailock]

i cant have one table cause there is a lot of cells i use.
yes the combination of these 2 is unique.
how i can do this ?
i have some cells with the same name for example in one table i have 1a,1b,1c... and in the other i have tha same nomenclature and in the forms i add the label.

[June7]

By cells you mean fields?

That still doesn't clarify to me why you have 20 tables. They are not identical structure?

UNION query (for some reason I cannot type UNION in the example query. Replace _______ with UNION:

SELECT 1 AS Source, field1, field2, field3 FROM table1
______ SELECT 2 AS Source, field1, field2, field3 FROM table2
....

Limit of 50 SELECT lines in a UNION. There is no designer or wizard for UNION, must type into SQL view of query builder.

[sailock]

if the field 1 from table 1 has tha same name as field 3 from table 4 for example is there any problem?
i have 20 tables because the tables represent documents and i formed them like the original documents.
every document has lnumber , st.date , and other varius informetions.
i can upload a part from my db to show u how i have done it.

[June7]

So every time you create a new document must create a new table? How often do you create new document?

First line of the query sets the field names and the data type. If you are combining fields with different names, can use the AS alias to create a field name.

[sailock]

this is a quick example about how my db looks like .

[June7]

Without data and more descriptive table and field names, hard to analyse structure.

Did you try the UNION?

[sailock]

i tried union but it doesnt do what i want , its just unite the tables.(very usefull btw )
i may not wording my db well .
for each lnumber-stDate i have 20 documents , its like one record , i upload the example db again with some "data " to show u how it works.example.zip

[June7]

That sort of dummy data is no more helpful than blank fields. Should we assume all these fields except lnumber and stDate are just free text data entry?

"it's like one record" doesn't make sense to me.

Uniting the tables fits what you describe in original post - that means UNION.

If you want to join the tables on the common lnumber and stDate fields, that will not be successful either because not every table has the same lnumber and dates as other tables. The 3 tables would have to be joined to a dataset of all possible lnumber/stDate pairs. And that means a UNION query.

SELECT lnumber, stDate FROM table1
______ SELECT lnumber, stDate FROM table2
______ SELECT lnumber, stDate FROM table3;

Now use that query in another query where each of the tables is joined to the UNION on the two common fields - this is a compound link.

[sailock]

That sort of dummy data is no more helpful than blank fields. Should we assume all these fields except lnumber and stDate are just free text data entry?

exactly , and some ye/no or dates.

"it's like one record" doesn't make sense to me.

sorry my english are not very good , with that i mean that lnumber and stdata represents 1 "item" ex. your name and ur birth date and this item has 20 forms .


to make it more clear with steps.
step1) i use union query to unite the lnumber and stdate from all tables ,
step2)i didnt understand what u mean , can u explain it .

thank u again for ur time

[June7]

The combination of lnumber and stdate is a unique identifier (compound key)

1. UNION query provides a dataset of all possible unique lnumber and stdate pairs

2. just build a query with the query designer
pull in the UNION query and the other 20 tables
link each of the 20 tables to the UNION query on the lnumber and stdate fields
linking on multiple fields is a compound link