Solved

Report Based on Mutiple table or queries ?

Posted on 2011-09-20
6
229 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 33

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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

778 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