Solved

joining data from two reports

Posted on 2013-01-03
5
357 Views
Last Modified: 2013-01-04
hello - am attaching sample database-
I have two reports- report1 and report2
originally created seperately- now being asked if I can join the data to display together-
in this way:
Category   item code    item description     YTD   JAN   etc.....

Best Buy     401          monitor            125    10   etc.....
 ( adding data from rpt2- that matches category right below)
             14          monitor part 1      60     10   etc...
             17          monitor part 2      90     15   etc...

HP           201          scanner            110     9   etc...

             143          scanner part 1     85      15  etc..
             144          scanner part 2     40      40  etc..

Is there anyway to have this data display like this -W/ form&subform?
thank you--

( rpt 1 based on tbl1/tblCategory/ and tblFinancial)
( rpt 2 based on tbl2/tblCategory2)
DataSample1.accdb
0
Comment
Question by:davetough
  • 3
  • 2
5 Comments
 

Author Comment

by:davetough
ID: 38741896
what I did not mention is that tbl1 and tbl2 are seperate tables in different db's- that are being updated daily- maybe I should join tables in a this db to create 1? an have some type of update process- keeping table up to date?
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 38741990
At the very least you would have to have the ItemCode in both tables.
You do, but the values do not match between the two tables?

If they did match, then you could create a simple Grouped report
*However* your tables have a slightly "un-normalized design" with "Months" as fields instead of Rows.
You also have the same fields in both tables (this is rare in a One to Many relationship)

Finally, neither table has a primary key, this further complicates the task of joining the data.
0
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 500 total points
ID: 38742053
Here is what I came up with:

see: rpttbl1
DataSample1.accdb
0
 

Author Closing Comment

by:davetough
ID: 38743489
thanks for your help
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 38744005
;-)
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
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.

895 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now