Dupicate Reports with subreport 2

[zapzip]

Hi all-

Help! I have a report (with query as record source) and a subrpeort (also with a second query). When prints get as many DUPLICATE shhets as there are records in subreport.

I have tried SELECT DISTINCTROW to no help.

Any ideas? Thanks--

 

[dhookom]

This is almost always caused by including detail records in your main report. Your main report must not have the same level of detail as the subreport.

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]

 

[zapzip]

Thanks for your reply.

Yes, the main report in fact has fields from the same table as the sub report.

What I was trying to accomplish, is to print a receipt. The main report includes the Payee,Date and total amount paid. The sub report included the type(s) of funds (check, money order, check, cash) and amount(s).

A typical output would be

PayeeName 8/8/2007 $500 (main Report)

$200 Cash
$200 Check
$100 Money Order (Sub Report)

Your suggestions would be appreciated aand thanks again for your comment

 

[dhookom]

You haven't provided anything about your table structures or record source SQL views. Again, you may need to remove a detail table from your main report or make the main report's record source a totals query.

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]

 

[zapzip]

thanks for you response and suggestions. I am not sure how to remove the detail from the main report in this case. I also have no clue about the total query. I've never done that (other than a sum ..) but would love to learn how.

The main reason for the subreport was that there can be more than one record of the type of funds data for each Receipt SLip. Perhaps there is another way?

Here is the detail:
I am using a form (F_ReceiptSlipSelPrint) to select which records to report/print. On form I select a check box if I want the record to print. After print an update query sets a fioed to “PRINT” thereby not allowing any further copies to be printed. Finally there is a command button On click event to start the report & RunSQL.
The forms record source is a query (Q_ReceiptSlipSelPrint).

Q_ReceiptSlipSelPrint
SELECT M_ReceiptSlip.ReceiptSlipGoPrint, M_ReceiptSlip.ReceiptSlipPrintFlag, M_ReceiptSlip.ReceiptSlipNumber, M_ReceiptSlip.ReceiptSlipActualPayee, M_ReceiptSlip.ReceiptSlipLeases_IDs, M_ReceiptSlip.ReceiptSlipDate, M_ReceiptSlip.ReceiptSlipDollarCollect, M_ReceiptSlip.ReceiptSlipPrintComment
FROM M_ReceiptSlip
WHERE (((M_ReceiptSlip.ReceiptSlipGoPrint)=False) AND ((M_ReceiptSlip.ReceiptSlipPrintFlag) Is Null));

The main report (R_ReceiptSlip) record source is a query. The report has 6 fields bound to this query: (Date, Receipt Number, Payee, Account, Total dollar amt).

Main Report Record source
SELECT Q_ReceiptSlipPrint.ReceiptSlipNumber, Q_ReceiptSlipPrint.ReceiptSlipLeases_IDs, Q_ReceiptSlipPrint.ReceiptSlipActualPayee, Q_ReceiptSlipPrint.ReceiptSlipDollarCollect, Q_ReceiptSlipPrint.ReceiptSlipDate
FROM Q_ReceiptSlipPrint
WITH OWNERACCESS OPTION;

The Subreport source object is
Report.RS_PayTypeFundsAllocation; this reports record source is Q_ReceiptSlip. There are only 2 fields in the subreport: Type Funds (FundPaidTypeFunds_IDs),and Amt (FundPaidAmount).

Q_ReceiptSLip
SELECT M_ReceiptSlip.ReceiptSlipGoPrint, M_ReceiptSlip.ReceiptSlipPrintFlag, M_ReceiptSlip.ReceiptSlipNumber, M_ReceiptSlip.ReceiptSlipLeases_IDs, M_ReceiptSlip.ReceiptSlipActualPayee, M_ReceiptSlip.ReceiptSlipDollarCollect, M_ReceiptSlip.ReceiptSlipDate, M_FundPaid.FundPaidReceiptSlips_IDs, M_FundPaid.FundPaidTypeFunds_IDs, 
M_FundPaid.FundPaidAmount
FROM M_ReceiptSlip INNER JOIN M_FundPaid ON (M_ReceiptSlip.ReceiptSlip_ID = M_FundPaid.FundPaidReceiptSlips_IDs) AND (M_ReceiptSlip.ReceiptSlip_ID = M_FundPaid.FundPaidReceiptSlips_IDs)
WHERE (((M_ReceiptSlip.ReceiptSlipGoPrint)=True) AND ((M_ReceiptSlip.ReceiptSlipPrintFlag) Is Null) AND ((M_ReceiptSlip.ReceiptSlipNumber)=[Forms]![F_ReceiptSlipSelPrint]![txtReceiptNumber]));

The all Receipt Slips should print up to 4 to a page.
The report correctly prints if there is a single record in the subform; however, if there are two records two receipt slips print; if 3 then 3 and so on.

There are only 2 table involved: M_ReceiptSlip and M_FundPaid.

M_ReceiptSlip
  ReceiptSlip_ID  (primary key)
  ReceiptSlipNumber
  ReceiptSlipDate
  ReceiptSlipDollarCollect
  ReceiptSlipActualPayee
  ReceiptSlipGoPrint
  ReceiptSlipPrintFlag
  ReceiptSlipSites_IDs
  ReceiptSlipSiteSubs_IDs
  ReceiptSlipLeases_IDs

M_FundPaid
  FundPaid_ID  (Primary Key)
  FundPaidAmount
  FundPaidComment
  etc
  FundPaidReceiptSlips_IDs {Foreign Key} to ReceiptSlip_ID 
  FundPaidTypeFunds_IDs                      above
  FundPaid

 

[dhookom]

Have you set the link master/child properties to ReceiptSlip_ID fields?

I am confused by part of your sql view where there seems to be a double join on the same fields:

FROM M_ReceiptSlip INNER JOIN M_FundPaid ON 
   (M_ReceiptSlip.ReceiptSlip_ID = M_FundPaid.FundPaidReceiptSlips_IDs) AND 
   (M_ReceiptSlip.ReceiptSlip_ID = M_FundPaid.FundPaidReceiptSlips_IDs)

I would expect something like:

FROM M_ReceiptSlip INNER JOIN M_FundPaid ON 
   (M_ReceiptSlip.ReceiptSlip_ID = M_FundPaid.FundPaidReceiptSlips_IDs)

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]

 

[zapzip]

Thanks again for your suggestions and reply. I corrected the duplicate AND in SQL. I have no clue where this came from. I usually rely on the design view to be correct. I have been doing a lot of trouble shooting/ changes so that’s probably how it happened. Not to make it a big point but should one check SQL vs design view frequently??

You also pointed out that the link properties needed attention- there were no links. When I try to add them there is an error message “The expression you entered refers to an object that is closed or doesn’t exist.” This despite the fact I haven’t even tried to enter a value- in fact I can not enter anything. The target value of main report does show up in Field List window as does the sub report value. This is probably the problem area, but I can't seem to fix it.

Thinking on paper- the main report record source is a query Q_ReceiptSlipPrint and the sub report record source is also Q_ReceiptSlipPrint. The main report query is an expression in the record source property, rather than a separate query. This expression has no tables but uses Q_ReceiptSlipPrint to generate fields. Maybe that helps?

I have also tried your sugestions about remove detail from main report but still don't know exactly how to keep the removed data on the report.

I have been working on this several days- but think the solution is at hand. Thanks for your advise and would appreciate any further suggestions.

 

[dhookom]

The link properties can be typed in and should be:

Link Master: ReceiptSlip_ID
Link Child: FundPaidReceiptSlips_IDs


I would change the main report's record source to either just the table M_ReceiptSlip or a query containing only M_ReceiptSlip.


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]

 

[zapzip]

Success!! Thanks for your suggestions each & everyone was exactly on target. I created a query for the main report with needed criteria, and “typed” in the links.

I greatly appreciate your help.

 

[strebor]

Duane -
Thank you for the link properties post. It helped me fix a problem. I didn't even have to struggle too much, because zapzip did most of the struggling. I just sort of shoulder-surfed!


strebor