?
Solved

Report Based on Mutiple table or queries ?

Posted on 2011-09-20
6
Medium Priority
?
241 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 34

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
Does Your Cloud Backup Use Blockchain Technology?

Blockchain technology has already revolutionized finance thanks to Bitcoin. Now it's disrupting other areas, including the realm of data protection. Learn how blockchain is now being used to authenticate backup files and keep them safe from hackers.

 
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

Get proactive database performance tuning online

At Percona’s web store you can order full Percona Database Performance Audit in minutes. Find out the health of your database, and how to improve it. Pay online with a credit card. Improve your database performance now!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Your data is at risk. Probably more today that at any other time in history. There are simply more people with more access to the Web with bad intentions.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Suggested Courses

770 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