How do I get a Form to show multiple table data

[magicdust]

Dear Genii...

I have 4 tables containing data, I have one form with a pretty UI - I need to get all the tables to show up when the people open the form.

I can either - import all data into one table - how do you do that? I can't allow the contact ID numbers to change if possible.

OR I need to link the form to grab the data from all 4 tables when its opened - how do you do that?

Sorry me newbie -- Thanks in advance
MD

 

[flyover789]

please post the table structure of the four tables

if all of your four tables have the same fields, you might consider a union query

HTH,
fly

[blue]Typos, that don't affect the functionality of code, will not be corrected.[/blue]

Martin Serra Jr.

http://www.mserrasystems.com

[blue]Database_Systems and _Applications shared across all Business_Areas[/blue]

 

[magicdust]

yes ables are all the same - so will up a union ... is it simple?

 

[flyover789]

something like this:

SELECT * FROM Table1
UNION
SELECT * FROM Table2
UNION
SELECT * FROM Table3
UNION
SELECT * FROM Table4;

HTH,
fly

[blue]Typos, that don't affect the functionality of code, will not be corrected.[/blue]

Martin Serra Jr.

http://www.mserrasystems.com

[blue]Database_Systems and _Applications shared across all Business_Areas[/blue]

 

[magicdust]

Hmmm....seems a bit above me...when I tried I don't know what to type...or how. Any ways me keep reading the help file...

Thanks

 

[flyover789]

go to the queries section of your DB. click new. select "Desgn View", click ok. In the select tables dialogue click cancel. in the toobar click on the SQL button. in the SQL window paste the a.m. code and replace the tablenames (Table1 through Table4) by the names of your four tables.

HTH,
fly

[blue]Typos, that don't affect the functionality of code, will not be corrected.[/blue]

Martin Serra Jr.

http://www.mserrasystems.com

[blue]Database_Systems and _Applications shared across all Business_Areas[/blue]

 

[magicdust]

whoo hoo it worked!... but now how do I get the form to read the whole 5000 records (the union of all the 4 tables) instead of just one table at a time?

 

[flyover789]

in your forms design view, in the "Data" Tab, select your union query as a Record Source.

HTH,
fly

[blue]Typos, that don't affect the functionality of code, will not be corrected.[/blue]

Martin Serra Jr.

http://www.mserrasystems.com

[blue]Database_Systems and _Applications shared across all Business_Areas[/blue]

 

[magicdust]

haha found it!!! yay it worked...thank you so much - you are tonights honary Genuis!!! Blessings

Cheers
MD

 

[magicdust]

Eeekkk its not over yet...now its saying record set is not updateable, when I go to edit a field. Any ideas...??

 

[flyover789]

there's no way to edit data in a union query.

if the merging of the four tables is a one time approach, that means if after merging them you will always work on the merged table and the four old tables will be history, you could create a maketable query based on the union query, and set the Recourd Source of your form to the new created table:

Maketable:

SELECT * INTO MyMergedTable FROM myUnionQuery;

HTH,
fly

[blue]Typos, that don't affect the functionality of code, will not be corrected.[/blue]

Martin Serra Jr.

http://www.mserrasystems.com

[blue]Database_Systems and _Applications shared across all Business_Areas[/blue]

 

[magicdust]

All time - double Genius Champion >>> Fly!
Night night me sleep now - all the data is sorted for tomorrow...thanks millions...

Magic