Solved

joining data from two reports

Posted on 2013-01-03
5
392 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
[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
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

Enroll in May's Course of the Month

May’s Course of the Month is now available! Experts Exchange’s Premium Members and Team Accounts have access to a complimentary course each month as part of their membership—an extra way to increase training and boost professional development.

Question has a verified solution.

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

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
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…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
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.

734 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