Remittance Building using a Report - Multiple data sources

[rm.harper]

Hi,
I'm hitting that never ending brick wall again! :banghead:

So my database is coming along nicely. Until a credit controller threw a spanner in the works and asked that i include partial credits into a remittance (using a report). Now this wouldnt be a problem if i knew exactly what im doing. Unfortunately my talents stop at [open new access database]

Anyway my problem is as follows, hopefully someone can just point me in the right direction.

My database stores invoices and credits and lets users input information manually. I have put together a remittance using a report which gathers all invoices with a 'Due' status and collates them into a handy remittance to send with a cheque. Which is all well and good when the credit controller on the other end doesn't mind working with me. However I've been asked to include any partial credits relating to an invoice. Which means although the payment figure is correct and takes into account the credit against the original it does not show THAT credit note within the remittance.

So my problems starts here. Although i have a relationship which links a credit note to the invoice it relates to, im struggling including the credit information within my remittance. Every way ive tried leads the remittance to only show invoices which have credits against them.

Can someone please explain how i can get my report to include credit information from my tblCredits when an invoice from tblInvoices has a credit against it and just lists the invoices as it already does when no credit has been issued against it.

Apologies if i havnt included enough information. I cant really explain myself sometimes.

I know i can somehow include credits in my remittance but i wish to JUST list credits relating to invoices shown on the remittance.

Many thanks,

 

[Minty]

I think we could do with some sample data / a layout to see why you are struggling?
Maybe a picture of the report as is with what you are expecting as well.

 

[rm.harper]

I think we could do with some sample data / a layout to see why you are struggling?
Maybe a picture of the report as is with what you are expecting as well.

OK, Sorry about that. So my report is based on the below query. The criteria relates to invoice 'status'. Which picks the invoices which are 'Due' and lists them in the remittance.

All i want to do is add [CreditDate], [CreditNum], [CreditAmount] to the remittance. Not all invoices will have a credit against them but for those that do i want them to also be listed within the remittance. Problem is whenever i try to include these details it then only shows invoices WITH a credit against them. (ive tried creating another query and base a new report on both, also tried just adding the fields and a few other methods too) with no luck.

I think there is a criteria or expression for what i am trying to do i just dont know what it is.

So in my head:
IF [InvoiceNum] has a 'linked record' with [InvoiceRef] show: [CreditDate], [CreditNum], [CreditAmount]

as well as the fields already included within the report.

Thanks

 

[pbaldy]

The resolution to "whenever i try to include these details it then only shows invoices WITH a credit against them" is to edit the join between tables in the query. Choose the appropriate option. It will change INNER in SQL view to LEFT or RIGHT.

 

[rm.harper]

The resolution to "whenever i try to include these details it then only shows invoices WITH a credit against them" is to edit the join between tables in the query. Choose the appropriate option. It will change INNER in SQL view to LEFT or RIGHT.

Could you describe further sorry. I didnt understand that. Youll have to excuse me im self taught and i know hardly any terminology.

Thanks

 

[pbaldy]

Double click or right click on the join line between the invoice number fields in the query.

 

[smig]

Inner join mean you only get records exist and match on both tables.

Left or Right joins mean you will get all the records in one table and only the matching ones (Left or Right) from the other table.

just do what pbaldy said.

 

[rm.harper]

Thanks very much for that input! It worked and now my Remittance (report) now shows ALL invoices (matching query criteria) along with matching credits to listed invoices!

Shows how easy it is when you know what your doing

Now going forward with the upgrades on my database. Im sure ill be posting another question soon. haha

Thanks again everyone! Much appreciated

 

[pbaldy]

Happy to help!

 

[rm.harper]

With all this now sorted. Is there anyway I can choose to select other records out of the query criteria that I can have listed on my report?

My criteria selects the correct records to be listed so I don't want to change it. But I may wish to include other records that do not meet the criteria? Can this happen? even if it is out of the access programme?

Thanks

 

[Minty]

In your query design there are many blank rows beneath where you have <=Date() for instance. These act as OR criteria so you could add another set of criteria along one or more rows depending on what you want to see.

E.g where you have "Due" AND <=Date() you could just add "Overdue" so you would see all overdue records regardless of date OR ones that fit your original criteria.

 

[rm.harper]

Will try the multiple criteria but I have a feeling I will need to re-organise my records for future usage.

Now I am involving more complicated functions im finding it hard to do the task based on this very simple database build.

With that remittance its not as easy as just including the credits now after testing all of yesterday.

I am beginning to think that I should involve another table for 'Payments'.

When I build my remittance it grabs raw data from invoices and sums them all up which is how I get my monthly bill together to pay. However it has become apparent that one invoice may have 2 amounts paid against it meaning that I would have to change the 'Invoice Amount' to match how much I want to pay off that invoice in that remittance. Which will become very messy wont it.

A lot of thinking needs to be done now to build a remittance method that does not affect any records but does this job for me.

 

[Minty]

Your Credits table should include any method of payment - probably including payments, just identify the payment type (Eg Credit Note, BACS, Cheque, Contra) but you need to sit down with a pen and paper and work out how this would work in your situation.

Accounting is notoriously awkward which is why people tend to go and buy something of the shelf as DIY packages are many months / years worth of work to get right.

 

[rm.harper]

Your Credits table should include any method of payment - probably including payments, just identify the payment type (Eg Credit Note, BACS, Cheque, Contra) but you need to sit down with a pen and paper and work out how this would work in your situation.

Accounting is notoriously awkward which is why people tend to go and buy something of the shelf as DIY packages are many months / years worth of work to get right.

Your telling me! :banghead:

I am trained on SageAccounts, but thought I would give this a shot so I can design my own interface and documents. May have to deal with Sage and its boring look ha