?
Solved

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

Posted on 2003-03-25
14
Medium Priority
?
248 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
[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
  • 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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

Industry Leaders: 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

Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
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 Month11 days, 11 hours left to enroll

752 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