Reports fields doubling

[Ringers]

I have used the reports wizard to try to show single records but it is showing the ref no more than once.

I just want the details for that single ref no, my fields are as follows:

Brand Name, DateRecevied, RefNo, Details, DocID, Description, person, date compelted.

I want the report to show a brand, then by recevied date (ascending) and all the details for a single RefNo. At the moment it sorting by brand but the same recevied date and refno more than once. So i am getting the same details repeated. (The ref no is linked to a unique Doc id, but sometimes the ref no can me used in another brand.)

At the moment:

Brand name XYY
recevied date 010207, refNo 1234, other fields
recevied date 010207, refno 1234, other fields (same line repeated)


I need it to show each date once and each refno once, can anyone help me out.

Want:

Brand Name XYY
received date 010207, refno 1234, other fields
recevied date 010308, refno 1255, other fields
Brand Name XYZ
recevied date 010207, refno 1234, other fields
recevied date 010607, refno 1257, other fields

thanks a lot, hopes this makes sence.

 

[Spenney]

Sounds like you need to group your data - either in the source of your report, or by using group headers in the report. What is the source of your report? A table, a query? Is the reference number repeated in that?

 

[Ringers]

Some of the fields are from tables some from queries. In the wizard I have grouped by brand and then on the next screen filtered by 'received date' and 'ref no', but there are repeated lines of the same 'ref no'.

 

[Spenney]

When I say, what is the source of your report? I mean what is the property of 'Record Source' set to? ie what object did you select as the source for the report's data on the very first page of the wizard. It will be a table or a query. I suspect it is a query which when executed repeats each RefNo.

To eliminate the repitition you will have to 'Group By' the RefNo in either the source data or the report.

Perhaps if you post the SQL of your query I may be able to help more.

 

[dhookom]

I also expect the issue is with your query/record source. Your statement "but sometimes the ref no can me used in another brand" suggests duplicates.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Ringers]

Here is the report as SQL. It has Inner joins, I am very rusty with advanced SQL like this, so any help would be great, thanks guys.

SELECT tblBrand.BrandName, qryBrandsFormlns2.FormlnTxt, tblActualIncomingDocs.Received, tblActualIncomingDocs.IncomingDocID, tblActualIncomingDocs.RefNo, tblActualIncomingDocs.Description, tblActualIncomingDocs.Resp, tblActualIncomingDocs.DateDue, tblActualIncomingDocs.DateCompleted, tblActualIncomingDocs.InitiatorInformed

AS tblActualIncomingDocs_InitiatorInformed, tblActualIncomingDocs.HAAction, tblActualIncomingDocs.Comments, tblRegActivity.DateSub, tblRegActivity.DateTGADesicion, tblRegActivity.InitiatorInformed AS tblRegActivity_InitiatorInformed

FROM (tblRegEvent INNER JOIN ((tblActualIncomingDocs INNER JOIN ((tblBrand INNER JOIN qryBrandsFormlns2 ON tblBrand.BrandID=qryBrandsFormlns2.BrandID)

INNER JOIN tblFormlnDoc ON qryBrandsFormlns2.FormlnID=tblFormlnDoc.FormlnID) ON tblActualIncomingDocs.ActIncDocID=tblFormlnDoc.ActIncDocID)
INNER JOIN tblDocTGAApps ON tblActualIncomingDocs.ActIncDocID=tblDocTGAApps.ActIncDocID) ON tblRegEvent.RegEventID=tblDocTGAApps.RegEventID)
INNER JOIN tblRegActivity ON tblRegEvent.RegEventID=tblRegActivity.RegEventID;

 

[dhookom]

I would expect that one side of each join should normally be a unique field or combinations. When you look at the record source in datasheet view, do you see exactly the records you expect?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Ringers]

The fields I am seeing are right, it is just duplicating the dateReceived and refNo on the report.

SO i need a way to show a record only once, Brand has many different refNo but is showing the same number multiple times.

 

[dhookom]

Are you seeing the number of records in the datasheet that you expect or are there duplicates? It's difficult to understand your sql without a brief description like
table1 is related 1 to many table2
table1 is related 1 to 1 table3
....

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]