Invoice Statement Report

[Seph]

Good day everyone,

I've been struggling with this for almost a week now.

Was hoping one of the geniuses on here might have any idea's on how to resolve this.

I have an unbound form StatementF with 3 unbound combo boxes on it. Namely CustomerName, StartDate and EndDate.

I then have a Query StatementRQ that filters data in my InvoiceT table based upon the criteria of the StatementF form.

There is then a report StatementR which uses that query as its record source.

For the most part it works, except for where it makes a duplicate page for each invoice:

I've tried all my tricks, with no success.

I've attached a Sample database which will hopefully shed more light.

I appreciate any advice.

Thank you!

 

[plog]

I just can't help myself, whenever someone posts a database I immediately go to the Relationship Tool. Because if that's not right, there's no point trying to fix downstream issues. You have too many relationships.

There should only be one way to travel from one table to another, you've created multiple loops. From Stock to Jobs you can travel 4 paths (Stock-Jobs, Stock-Invoice-Jobs, Stock-Invoice-Travel-Jobs, Stock-Invoice-Clients-Job). That's wrong. Stock should only be directly related to Invoice or Jobs, not both (educated guess-probably just Invoice since that is at a lower level.)

You've made that mistake a few times. Customer Clients should probably just go to Jobs and Travel should probably just go to Invoice. I suggest you clean that up before you work on your reporting issue.

For your report--you've over-engineered it. Seems like you just want a report of all invoices for a customer in a time frame. This only takes 1 report based on StatementSRQ. The query StatementRQ does nothing for you--you already have the customer name in STatementSRQ, Ditch the main form and use the sub-form as the main form, just add a grouping section for the customer at the top and format it to look like what the main report is now.

Lastly, you've taken a weird approach to implementing your criteria. In your VBA you apply a filter when you DoCmd.OpenReport report to look at a specific customer. But then you also apply criteria in your query for the date. I suggest you strip all criteria out of your query and use the DoCmd.OpenReport criteria for everything. This way everything works indepenendtly--you can open the queries, you can open the report without having to also have that form open. If you ever need to see all the data you have that option available with the DoCmd.OpenReport method. Further, you can easily give that ability to your users by only filtering what is needed. As it is now, they get an error if they leave out a date or a customer.

Again though, fix your relationships

 

[Seph]

I just can't help myself, whenever someone posts a database I immediately go to the Relationship Tool. Because if that's not right, there's no point trying to fix downstream issues. You have too many relationships.

There should only be one way to travel from one table to another, you've created multiple loops. From Stock to Jobs you can travel 4 paths (Stock-Jobs, Stock-Invoice-Jobs, Stock-Invoice-Travel-Jobs, Stock-Invoice-Clients-Job). That's wrong. Stock should only be directly related to Invoice or Jobs, not both (educated guess-probably just Invoice since that is at a lower level.)

You've made that mistake a few times. Customer Clients should probably just go to Jobs and Travel should probably just go to Invoice. I suggest you clean that up before you work on your reporting issue.

For your report--you've over-engineered it. Seems like you just want a report of all invoices for a customer in a time frame. This only takes 1 report based on StatementSRQ. The query StatementRQ does nothing for you--you already have the customer name in STatementSRQ, Ditch the main form and use the sub-form as the main form, just add a grouping section for the customer at the top and format it to look like what the main report is now.

Lastly, you've taken a weird approach to implementing your criteria. In your VBA you apply a filter when you DoCmd.OpenReport report to look at a specific customer. But then you also apply criteria in your query for the date. I suggest you strip all criteria out of your query and use the DoCmd.OpenReport criteria for everything. This way everything works indepenendtly--you can open the queries, you can open the report without having to also have that form open. If you ever need to see all the data you have that option available with the DoCmd.OpenReport method. Further, you can easily give that ability to your users by only filtering what is needed. As it is now, they get an error if they leave out a date or a customer.

Again though, fix your relationships

Thank you very much for your input.

I will consider your words carefully while implementing the recommendations.

 

[Seph]

I just can't help myself, whenever someone posts a database I immediately go to the Relationship Tool. Because if that's not right, there's no point trying to fix downstream issues. You have too many relationships.

There should only be one way to travel from one table to another, you've created multiple loops. From Stock to Jobs you can travel 4 paths (Stock-Jobs, Stock-Invoice-Jobs, Stock-Invoice-Travel-Jobs, Stock-Invoice-Clients-Job). That's wrong. Stock should only be directly related to Invoice or Jobs, not both (educated guess-probably just Invoice since that is at a lower level.)

You've made that mistake a few times. Customer Clients should probably just go to Jobs and Travel should probably just go to Invoice. I suggest you clean that up before you work on your reporting issue.

For your report--you've over-engineered it. Seems like you just want a report of all invoices for a customer in a time frame. This only takes 1 report based on StatementSRQ. The query StatementRQ does nothing for you--you already have the customer name in STatementSRQ, Ditch the main form and use the sub-form as the main form, just add a grouping section for the customer at the top and format it to look like what the main report is now.

Lastly, you've taken a weird approach to implementing your criteria. In your VBA you apply a filter when you DoCmd.OpenReport report to look at a specific customer. But then you also apply criteria in your query for the date. I suggest you strip all criteria out of your query and use the DoCmd.OpenReport criteria for everything. This way everything works indepenendtly--you can open the queries, you can open the report without having to also have that form open. If you ever need to see all the data you have that option available with the DoCmd.OpenReport method. Further, you can easily give that ability to your users by only filtering what is needed. As it is now, they get an error if they leave out a date or a customer.

Again though, fix your relationships

Hi again @plog

I took your advice and made some changes.

Thanks again for taking the time to advise me.

Kindly see below updated Sample database.

"Anyone else who would like it as a template is welcome to use it "