?
Solved

Data report from two tables ("Header" & "Lines")

Posted on 2003-03-25
14
Medium Priority
?
249 Views
Last Modified: 2013-12-25
I am trying to create a data report which displays information from two tables, the first is the “header” (the information that’s supposed too appear only once per page) and the second the “Lines” (the information that can appear several times) can someone tell me how I can do this (that is display the information on the data report)

     Thanks in advance
          Yaakov
0
Comment
Question by:YaakovS
  • 6
  • 3
  • 3
  • +2
14 Comments
 
LVL 4

Expert Comment

by:bistrica
ID: 8208505
If you need data from two tables, then combine them in one query and bring that set into report.
In header of report insert data from fields relevant to header. In body of report insert relevant data.

Report is based on "one" recordset, but recordset can be combined of more than one table or query.
0
 

Expert Comment

by:tat83
ID: 8208508
hi
firstly , u need to have a dataenvironment
then u need to add the command
type in ur sql statement
then u need to add a data report
after u add the data report
drag the information that u want to the header of the data report
0
 

Expert Comment

by:tat83
ID: 8208518
it was hard to explain in word
if u would like have a sample from me
please leave ur e- mail here
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 5

Author Comment

by:YaakovS
ID: 8210751
The difficulty I’m having is to get It to display the “lines” as many times as needed while only displaying the “header” information once.
0
 
LVL 4

Expert Comment

by:bistrica
ID: 8210886
I have not worked with data report control in VB. I was using Access db for reports. So, I don't know all the details.
Anyway, if you have all your data in report drag and drop to header fields that you want in header.

I assume that this can be doen in report design mode.
0
 
LVL 5

Author Comment

by:YaakovS
ID: 8220251
The problem with doing that is that it repeats all the header information for every Line (that is if there is more then one matching record in the second table, it will repeat the information from the first table as well).

I tried to make a hierarchical relationship between the two tables and that worked fine when the second table selected the entire table (i.e I set the source of data data object to table), but when I set it to select with a sql statement It gives me a error “Data Provider  or other service returned an E_FAIL status” does anyone know what the problem is? Thanks.
0
 
LVL 5

Author Comment

by:YaakovS
ID: 8222350
I want to add that when I set the Sql in the child command to select everything goes fine, but when I set it to create a new field based on a calculation of two of the existing fields (field1 * field2 as field3) I get the error message I posted earlier, though I tested the sql statement in the sql builder in it seemed to work fine.
0
 

Expert Comment

by:tat83
ID: 8223132
can u please show ur sql here?
0
 
LVL 5

Author Comment

by:YaakovS
ID: 8225217
Ok here's the SQL statement that works
SELECT Item.* FROM Item

And here's the one that's giving me the error
SELECT Item.*, `Item Quantity` * `Item Price` AS `Per Item Total` FROM Item
0
 
LVL 4

Expert Comment

by:bistrica
ID: 8225421
What are these: `

Try:
SELECT Item.*, [Item Quantity] * [Item Price] AS [Per Item Total] FROM Item
0
 
LVL 5

Author Comment

by:YaakovS
ID: 8226119
These ` work in the dataEnviromment (if you use the SQL builder it uses those instead of [])

Anyway i tried that and i get the same error, but i noticed that while i thought it works in the SQL builder (test run) it loads it all up, but when i scroll down when i get to the end of the list it gives me that error.
0
 
LVL 5

Author Comment

by:YaakovS
ID: 8232745
Never Mind I have solved the problem.Can someone delete this thread,Thanks.
0
 
LVL 1

Expert Comment

by:ayufans
ID: 9272277
No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:

- PAQ'd and points refunded

Please leave any comments here within the next seven days.

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER !

ayufans
Cleanup Volunteer
0
 

Accepted Solution

by:
AnnieMod earned 0 total points
ID: 9335050
Per recommendation.

AnnieMod
Community Support Moderator
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
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…
Suggested Courses
Course of the Month8 days, 3 hours left to enroll

616 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