Unbound form display result of seven queries

[jimbo62]

I have an unbound form and I have seven queries that count the number of records. I need to display totals for each "Open Picks per Wave" in the form. I want to have each result displayed in a text box. I will set up a button to requery and update the linked table for updates.

Previously I set up the form initially bound to one query and run a subform for each of the other queries. I can run each query and get my results. This seems too cumbersome plus I cant make it look as clean as I want it to be.

Any Suggestions?

Jimbo

 

[scriverb]

The way that I link multiple unrelated queries together is I create a new column(field) in each query and name it LINK. I give it a value of "LINK". Now using one query as the primary I can INNER JOIN to all the other queries using the LINK field as the common field.

Select "LINK" as LINK, Sum(A.SalesValue) as TotalSales
FROM tblMyTable as A;

Do the same for all the queries then link them together in the query design window of a final query using LINK as the common field. This query then can be used as input to your form or report.

Let me know if this is what you were looking for.

Bob Scriver

Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[jimbo62]

That is a good suggestion. I would have to update my tables to do this the table is a linked table to an ODBC data source. I have a form built now where users choose the tab for the appropriate area of the wharehouse.

Is there possibly any other way?

Thanks again


Jimbo

 

[scriverb]

No, you don't have to update any tables. The queries after they are built and when you run them will have this new field that can be used to link together. This is a calculated field in each of the queries. The a final query uses all of the new queries with the new field to link together using the new field.

Got it?? Clear as mud??

Saved as Query1:
Select "LINK" as LINK, Sum(A.SalesValue) as TotalSales1
FROM tblMyTable1 as A;

Saved as Query2:
Select "LINK" as LINK, Sum(A.SalesValue) as TotalSales2
FROM tblMyTable2 as A;

Saved as Query 3:
Select "LINK" as LINK, Sum(A.SalesValue) as TotalSales3
FROM tblMyTable3 as A;


Now to combine them all save this SQL as QueryLINK:
Select A.TotalSales1, B.TotalSales2, C.TotalSales3
FROM (TotalSales1 as A INNER JOIN TotalSales2 as B ON A.LINK = B.LINK) INNER JOIN TotalSales3 as C ON A.LINK = C.LINK;

Does this now make sense to you. Your query to the ODBC data source table can just be one of the saved queries with the manufactured LINK field.



Bob Scriver

Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[scriverb]

Sorry, slight typo here:
Now to combine them all save this SQL as QueryLINK:
Select A.TotalSales1, B.TotalSales2, C.TotalSales3
FROM (Query1 as A INNER JOIN Qluery2 as B ON A.LINK = B.LINK) INNER JOIN Query3 as C ON A.LINK = C.LINK;


Bob Scriver

Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???