Select querie for a report

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

When I creat a query to draw information from 2 tables for a report, the end
result shows the values i have selected, but if table "A" only has three
results and table "B" has 1 result, the end table has duplicated information.
Why is this?
 
Because you are combining the rows of the two tables and each combination
has to have a row for its output.

If the rows are exactly the same in ALL the fields that are being displayed
then you can use the DISTINCT operator to only show the unique rows. You
can do this in the query design grid by following these steps.

Open the query in Design View
Select View: Properties from the menu
Click anywhere in the grey area at the top (not on any table)
Set the Unique values property to Yes
Run the query
 
But my tables do not consist of the same data. The main idea is that i have a
report: The first section of the report will display data from the first
table. Then in the second part on the same report i will have data from the
second table. Im really struggling with this, please help i'm frustrated!
 
If the two tables are not related then you might need to use a sub-report in
the main report.

I'm sorry, but I am not able to figure out your problem sufficiently to
offer you any detailed advice. First step, can you post the report's record
source? It is probably a query since you seem to be getting records from
two tables.

(Possibly unneeded instructions follow)
Open the query
Select View:Sql from the Menu
Select all the text
Copy it
Paste it into the message

If your record source is not a saved query, then it could be that you need
to get the query by opening the report in design view, clicking on the three
dots at the end of record source, then follow the above steps.
 
SELECT HistoryLines1.DDate, HistoryLines1.DocumentNumber,
HistoryLines1.CostCode, HistoryLines1.DiscountAmount,
HistoryLines1.Description, LedgerTransactions2.GDC,
LedgerTransactions2.DDate, LedgerTransactions2.EType,
LedgerTransactions2.Refrence, LedgerTransactions2.JobCode,
LedgerTransactions2.Amount, LedgerTransactions2.Description
FROM LedgerTransactions2, HistoryLines1
WHERE (((HistoryLines1.DDate)>=[forms]![ReportSelect]![DTPicker5] And
(HistoryLines1.DDate)<=[forms]![ReportSelect]![DTPicker6]) AND
((HistoryLines1.CostCode)=[Forms]![ReportSelect]![List81].[value]) AND
((LedgerTransactions2.DDate)>=[forms]![ReportSelect]![DTPicker5] And
(LedgerTransactions2.DDate)<=[forms]![ReportSelect]![DTPicker6]) AND
((LedgerTransactions2.JobCode)=[Forms]![ReportSelect]![List81].[value]));
 
I suspect that LedgerTransactions2 is related to HistoryLines1
using LedgerTransactions2.JobCode = HistoryLines1.CostCode. If that is so,
then your SQL should probably look more like the SQL below. This doesn't
change the situation much, but it does change it a bit.

In a report, you might sort by the relevant fields in the first table and
then set the hide duplicates property of each control in the first table to
hide duplicates. You could also group by the relevant primary key of the
one table and put all the data into the group and then in the details of the
report put all the relevant data from the other table.

Use the report's Grouping and Sorting dialog to set up your group and the
sort of the detail lines.

SELECT HistoryLines1.DDate
, HistoryLines1.DocumentNumber
, HistoryLines1.CostCode
, HistoryLines1.DiscountAmount
, HistoryLines1.Description
, LedgerTransactions2.GDC
, LedgerTransactions2.DDate
, LedgerTransactions2.EType
, LedgerTransactions2.Refrence
, LedgerTransactions2.JobCode
, LedgerTransactions2.Amount
, LedgerTransactions2.Description
FROM LedgerTransactions2 INNER JOIN HistoryLines1
ON LedgerTransactions2.JobCode = HistoryLines1.CostCode
WHERE HistoryLines1.DDate>=[forms]![ReportSelect]![DTPicker5] And
HistoryLines1.DDate<=[forms]![ReportSelect]![DTPicker6] AND
HistoryLines1.CostCode=[Forms]![ReportSelect]![List81] AND
LedgerTransactions2.DDate>=[forms]![ReportSelect]![DTPicker5] And
LedgerTransactions2.DDate<=[forms]![ReportSelect]![DTPicker6]

Charles de Bruyn said:
SELECT HistoryLines1.DDate, HistoryLines1.DocumentNumber,
HistoryLines1.CostCode, HistoryLines1.DiscountAmount,
HistoryLines1.Description, LedgerTransactions2.GDC,
LedgerTransactions2.DDate, LedgerTransactions2.EType,
LedgerTransactions2.Refrence, LedgerTransactions2.JobCode,
LedgerTransactions2.Amount, LedgerTransactions2.Description
FROM LedgerTransactions2, HistoryLines1
WHERE (((HistoryLines1.DDate)>=[forms]![ReportSelect]![DTPicker5] And
(HistoryLines1.DDate)<=[forms]![ReportSelect]![DTPicker6]) AND
((HistoryLines1.CostCode)=[Forms]![ReportSelect]![List81].[value]) AND
((LedgerTransactions2.DDate)>=[forms]![ReportSelect]![DTPicker5] And
(LedgerTransactions2.DDate)<=[forms]![ReportSelect]![DTPicker6]) AND
((LedgerTransactions2.JobCode)=[Forms]![ReportSelect]![List81].[value]));


John Spencer said:
If the two tables are not related then you might need to use a sub-report
in
the main report.

I'm sorry, but I am not able to figure out your problem sufficiently to
offer you any detailed advice. First step, can you post the report's
record
source? It is probably a query since you seem to be getting records from
two tables.

(Possibly unneeded instructions follow)
Open the query
Select View:Sql from the Menu
Select all the text
Copy it
Paste it into the message

If your record source is not a saved query, then it could be that you
need
to get the query by opening the report in design view, clicking on the
three
dots at the end of record source, then follow the above steps.
Click to expand...
Click to expand...
 
Back
Top