Aged Analysis Query (1 Viewer)

[LOUISBUHAGIAR54]

One o the tables on my database is called InvDetails. It contains details of the invoices that the company receives from various suppliers. Its fields the the following:

Supplierefno
Invoiceref
Describtion
amount
Dateissued
Pay_Ref
Trustee
Invoicefileref
paid

The supplierefno links to another table with supplier's details
The invoiceref fields contains a unique number of the invoice
The amount is the amount of the invoice in euros
Dateissued is the date on the invoice.
Pay_ref links to a table containing details of the payment when the invoice is eventually settled.
Trustee is the name of a person who checks the validity of the invoice
Invoicefileref denotes when the hardcopy of the invoice is filed, and
Paid is a Yes/no field denoting whether the invoice is paid.

I would like to draw up a report called an Aged Analysis report which groups the sum of unpaid invoices by name of company, due more than 30 days, due more than 60 days, and due more than 120 days.

Can somebody helps with what sort of queries are necessary and in which order ? Many thanks for any help. I have not managed by myself.

Louis

 

[burrina]

Here is an example.Adapt to your needs.

DateDiff("d",[DateDone],Date()) > 30

HTH

 

[LOUISBUHAGIAR54]

I am trying to answer my own question. Since I last posted it I have figured out that I have to create a first query based on the table. The query has to create a field using nested iif functions to assign a number depending whether the invoice has expired by 0,30,60 or 120 days. It assigns 0,1,2,3 respectively. One then needs to design a cross-tab query with the numbers 0,1,2,3 as the column headings, the supplierefno as the row headings and then sum of the amount at the crossings of rows and columns.

A report is then based and cleaned based on this crosstab query with the names of the company linked to supplierefno.

I am trying to get this to work. Should anyone have a better more sleak way round this, please let me know.


Louis

 

[bob fitz]

Louis
Take a look at the query called "qryMain0to365" in the attached db. Perhaps this similar to what you want?

 

[LOUISBUHAGIAR54]

Hi Bob,

Last week we had an encounter with regards to a form which would not load to add a new record as a FormAdd. I had posted you my db but received no reply. In any case I solved that problem myself. I am still interested in any comments you might have to make with regards to my db in general. You seem to be quite experienced and any suggestions would be gratefully appreciated.

With regards this query, I have managed to sort this out with some assistance elsewhere also from this forum. I proceeded as above. I used a nested IIf condition in a query based on the above table. I thus divided the records into four categories marked "Due immediately", "Due in 30 days", "Due in 60 days", "Due in 90 days", "Due in 120 days", depending on the following nested IIf function.

Class: IIf(Date()>[Due],"Due immediately",IIf(Date()+30>[Due],"Due in 30 days",IIf(Date()+60>[Due],"Due in 60 days",IIf(Date()>120,"Due in 120 days",0))))

The field [Due] is based on the dateissued and the credit time given.

A crosstab query is based on this query with sum of amounts in the intersections of the rows and columns. one needs to make sure the one names the list of columns in the crosstab column query properties

NB. I cannot download your db. How can I do it ?

Many thanks.

 

[bob fitz]

You should be able to download the db by just clicking on it.

 

[LOUISBUHAGIAR54]

Thanks

When I click it I get a file called attachment.php which does not turn into an access database.


I seem to get all the unexpected results


In any case thanks. There used to be a button to thank people which I do not seem to find either.

Louis

 

[bob fitz]

There used to be a button to thank people which I do not seem to find either.

Its at the bottom right side of each of my posts.

 

[namliam]

Thanks

When I click it I get a file called attachment.php which does not turn into an access database.


I seem to get all the unexpected results


In any case thanks. There used to be a button to thank people which I do not seem to find either.

Louis

This is a known issue from the newest/newer version of Internet explorer, unfortunatly forums all over the place having issues with it.

Try using chrome or another browser instead.

 

[bob fitz]

Thanks

When I click it I get a file called attachment.php which does not turn into an access database.


I seem to get all the unexpected results


In any case thanks. There used to be a button to thank people which I do not seem to find either.

Louis

I don't know why it won't work for you unless it has something to do with it being in an earlier format. Perhaps the this will be better: