• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 246
  • Last Modified:

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*]
ProjectNo
VONo


Table 2 - Material Cost Table

ID   [*Note Relationship with Table 1 id]
MaterialCost
MaterialMargin
MaterialValue

Table 3 - Bolt Cost Table

ID   [*Note Relationship with Table 1 id]
BoltCost
BoltMargin
BoltValue

Table 4 - Fabrication Cost Table

ID   [*Note Relationship with Table 1 id]
FabricationCost
FabricationMargin
FabricationValue

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
etc...

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
0
Surveyor1
Asked:
Surveyor1
  • 3
  • 2
1 Solution
 
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.

0
 
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.

JeffCoachman
0
 
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.

Regards

Surveyor
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
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, ...post 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.

JeffCoachman
0
 
Surveyor1Author Commented:
No comment from others helped
0
 
Surveyor1Author Commented:
No Objection
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now