Probmen with union query

[sroy]

I have multiple table with I am joining then with UNION ALL as Union query. But the output data only shows only data from first two tables. Where I am doing wrong. I have selected similar column names of each excel sheet.

[June7]

Post the attempted query SQL statement for analysis.

[sroy]

Here it is:

SELECT table1.[Name], table1.[Number], table1.[Product Detail], table1.[Status], table1.Size, table1.Price
FROM table1
UNION ALL
SELECT table2.[Name], table2.[Number], table2.[Product Detail], table2.[Status], table2.Size, table2.Price
FROM table2
UNION ALL SELECT table3.[Name], table3.[Number], table3.[Product Detail], table3.[Status], table3.Size, table3.Price
FROM table3;
UNION ALL SELECT table4.[Name], table4.[Number], table4.[Product Detail], table4.[Status], table4.Size, table4.Price
FROM table4;
UNION ALL SELECT table5.[Name], table5.[Number], table5.[Product Detail], table5.[Status], table5.Size, table5.Price
FROM table5;

Post the attempted query SQL statement for analysis.

[June7]

That should work but try:

SELECT [Name], [Number], [Product Detail], [Status], Size, Price, "Table1" As Source FROM table1
UNION ALL SELECT [Name], [Number], [Product Detail], [Status], Size, Price, "Table2" FROM table2
UNION ALL SELECT [Name], [Number], [Product Detail], [Status], Size, Price, "Table3" FROM table3;
UNION ALL SELECT [Name], [Number], [Product Detail], [Status], Size, Price, "Table4" FROM table4;
UNION ALL SELECT [Name], [Number], [Product Detail], [Status], Size, Price, "Table5" FROM table5;


BTW, should not use reserved words as field names. Name is a reserved word, not sure about Number.

[sroy]

Now I want to make a query search form [to find matches in tables] and to display query results in a subform. I also need two button to save or print the query results. This subform part is tuff for me. I will be glad if you can help.

[June7]

I am not sure you need a form/subform arrangement. Review: http://datapigtechnologies.com/flash...tomfilter.html

Probably better to use a report for print outpupt. Also review: http://datapigtechnologies.com/flash...mtoreport.html

I don't use dynamic parameters in queries but it is a simple method with little code involved. My preference is to pass filter criteria to form or report like:

DoCmd.OpenReport "report name", , , "ID=" & Me.ID

[sroy]

Thank you.
From the union query I have created a new query and in design view of new query I have used parameters in createria.
And again from this new query I have created a search form. Now as the search form works it shows the result in new query but I want to get the search form results in a report. I am novice and have little knowledge of reports. please help.

[June7]

Set the report RecordSource property to the parameterized query.

[sroy]

Its not clear if you can give me an example Sir ? I am in learning phase.
From what I will create the report ? I mean by selecting new query or the search form I will create the report ?
Thanks again.

[June7]

Is the form RecordSource a saved Access query object with criteria parameters? Use the same query for the report. Build a report essentially the same way as building a form. This is basic Access functionality. Access Help has guidelines.

[sroy]

In my union query below is the scenerio

SELECT [Name], [Number], [Product Detail], [Status], Size, Price, "Table1" As Source FROM table1
UNION ALL SELECT [Name], [Number], [Product Detail], [Status], Size, Price, "Table2" FROM table2
UNION ALL SELECT [Name], [Number], [Product Detail], [Status], Size, Price, "Table3" FROM table3;
UNION ALL SELECT [Name], [Number], [Product Detail], [Status], Size, Price, "Table4" FROM table4;
UNION ALL SELECT [Name], [Number], [Product Detail], [Status], Size, Price, "Table5" FROM table5;

I have to open each table time to time for update in excel because each table is in .xlsx format.
My question is: How can I consider the updates in my access query even when access query form is open ?
Or do I need to close the access query form and then I have to make the updates?
Please help me with a solution code for a refresh button in both the cases ?

[June7]

Why is Excel involved?

If you edit data in Excel the edits will be passed to Access in the link. The form can be refreshed by clicking Refresh from the ribbon or code:

Me.Requery

[sroy]

OK what is the full refresh button code ? Help me on this.
Excel is involved because then I can enter data in less time I think.

[June7]

The code is:

Me.Requery

You can put that in the Click event of button on form.

How can entering data in Excel be faster than entering into Access tables? Build forms as interface with tables.

[sroy]

This has worked for me as you suggested
That should work but try:

SELECT [Name], [Number], [Product Detail], [Status], Size, Price, "Table1" As Source FROM table1
UNION ALL SELECT [Name], [Number], [Product Detail], [Status], Size, Price, "Table2" FROM table2
UNION ALL SELECT [Name], [Number], [Product Detail], [Status], Size, Price, "Table3" FROM table3;
UNION ALL SELECT [Name], [Number], [Product Detail], [Status], Size, Price, "Table4" FROM table4;
UNION ALL SELECT [Name], [Number], [Product Detail], [Status], Size, Price, "Table5" FROM table5;


But once I have made a query from unionquery - it is showing only two table data. I have created the query to put search parameters for my table. Please help.