Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

joining data from two reports

Posted on 2013-01-03
5
Medium Priority
?
411 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 2000 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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

783 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