Improve company productivity with a Business Account.Sign Up

x
?
Solved

Report Based on Mutiple table or queries ?

Posted on 2011-09-20
6
Medium Priority
?
247 Views
Last Modified: 2012-08-14
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
Comment
Question by:Surveyor1
  • 3
  • 2
6 Comments
 
LVL 37

Expert Comment

by:Norie
ID: 36565360
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36568270
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
 

Accepted Solution

by:
Surveyor1 earned 0 total points
ID: 36569949

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
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36570385
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
 

Author Closing Comment

by:Surveyor1
ID: 37233447
No comment from others helped
0
 

Author Comment

by:Surveyor1
ID: 37210171
No Objection
0

Featured Post

Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

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.

Join & Write a Comment

A Case Study of using the Windows API to provide RS232 communications capability in Access without the use of Active-X controls.
Are you working to mount the dismounted Exchange 2013 database? Then the best course of action is to analyze the causes of Database issue, their probable solutions and decide for the appropriate course of action.
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
Hi, this video explains a free download that you can incorporate into your Access databases, or use stand-alone for contact management. Contacts -- Names, Addresses, Phone Numbers, eMail Addresses, Websites, Lists, Projects, Notes, Attachments…

579 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question