Report Based on Mutiple table or queries ?

Experts Thank You in Advance

I am trying to create a report based on mutiple tables/ queries (Not sure if i need to create a intermediate query or not ?)

Based on the following Tables

Table 1 - VoSummary

Id [*Primary Key*]

Table 2 - Material Cost Table

ID   [*Note Relationship with Table 1 id]

Table 3 - Bolt Cost Table

ID   [*Note Relationship with Table 1 id]

Table 4 - Fabrication Cost Table

ID   [*Note Relationship with Table 1 id]

From the above tables I am looking to create a report (Based on Query Criteria on ProjectNo from a user form) simply as follows:  

Project No:  Project 1     **(Comes from criteria on query linked to form i.e [forms]![projectcost]!projectno]***  I am ok with this on a select but a crosstab will not accept criteria.

VONo   MaterialTotaCost    BoltTotalCost   FabricationTotalCost   VOTotals

1                        2,000                  1,000                         4,000              7,000
2                        1,000                  2,000                         5,000              8,000
3                        8,000                  3,000                         2,000            13,000
4                                                                                      4,000              4,000
5                        6,000                  8,000                         1,000            15,000
6                        2,000                  1,000                         9,000            12,000
7                        8,000                  3,000                         3,000            14,000

Totals              Total1                   Total2                         Total3            Total

I have got this working for 1 table for both a select query and crosstab query but I have gone blank on how to bring data from mutiple tables in the report format above.

Again many thanks for your help
Who is Participating?
Surveyor1Author Commented:

Many Thanks for your responses, However

I have tried this but it appears to cause problems with numbers doubling up etc...  works well with 1 table but then you add table 2 and the fiqures from 2 etc.. change and are incorrect.

A select query although as simple as it seems just does not seem to be the answer.


NorieVBA ExpertCommented:
Why not use a standard 'SELECT' query for the report?

You shouldn't need to use a crosstab to get the data you want and you can format the report using grouping etc.

Jeffrey CoachmanMIS LiasonCommented:
As for me I would rather see a sample db file to be sure of what I am proposing...

But I'm with imnorie, you have to try making a query with all of these tables first.
If the query gives you all the fields you need in the report, the Report can create any totals you need.

Network Scalability - Handle Complex Environments

Monitor your entire network from a single platform. Free 30 Day Trial Now!

Jeffrey CoachmanMIS LiasonCommented:
Then again, please post a sample database so that we can more closely examine you design

Sample database notes:
1. Back up your database(s).
2. Combine the front and back ends into one database file.
3. Remove any startup options, unless they are relevant to the issue.
4. Remove any records unless they are relevant to the issue.
5. Delete any objects that do not relate directly to the issue.
6. Remove any references to any "linked" files (files outside of the database, Images, OLE Files, ...etc)
7. Remove any references to any third party Active-x Controls (unless they are relevant to the issue)
8. Remove, obfuscate, encrypt, or otherwise disguise, any sensitive data.
9. Compile the code. (From the VBA code window, click: Debug-->Compile)
10. Run the compact/Repair utility.
11. Remove any Passwords and/or security.
12. If a form is involved in the issue, set the Modal and Popup properties to: No
    (Again, unless these properties are associated with the issue)
13. Post the explicit steps to replicate the issue.
14. Test the database before posting.

In other words, a database that we can easily open and immediately see and/or troubleshoot the issue.
And if applicable, also include a clear graphical representation of the *Exact* results you are expecting, based on the sample data.

Surveyor1Author Commented:
No comment from others helped
Surveyor1Author Commented:
No Objection
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.