Default Sales by Customer MS Access Report

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

iaqsuk - 27 Sep 2006 21:12 GMT

I'm using the Default MS Access Sales by Customer reports. Is it possible to
have a aging report by 0-30, 31-60, 61-90, 90+ with the total sales format?

Can anyone help me with this report. thank you.

Reply to this Message

Larry Linson - 28 Sep 2006 02:56 GMT

> I'm using the Default MS Access Sales by Customer reports. Is it possible
> to
> have a aging report by 0-30, 31-60, 61-90, 90+ with the total sales
> format?

_What_ "Default MS Access Sales by Customer" Reports are you talking about?
The ones in the Northwind Traders sample database or something else? Access
itself has no such Reports.

Larry Linson
Microsoft Access MVP

Reply to this Message

iaqsuk - 28 Sep 2006 19:09 GMT

Thank you for the response. Sorry, I didn't mention it earlier, I'm using
the MS Access "Order Entry" database. There is 2 reports I've tried:
"Receivables Aging" which does work, but it lists all the entries line by
line for every entry for that customber account. This report would work if
only listed the total sales and not every entry.

The other report is the 1 I mentions in MS Access "Order Entry" database
"Total Sales by Customber". This would work if we can list it in 0-30, 31-60,
61-90, 90+. Please let me know if this is possible. Thank you Arnold

>> I'm using the Default MS Access Sales by Customer reports. Is it possible
>> to
[quoted text clipped - 7 lines]
> Larry Linson
> Microsoft Access MVP

Reply to this Message

Larry Linson - 28 Sep 2006 19:39 GMT

Sorry. I know of the existence of this sample database, but have never used
it. Unfortunately, I do not have time and energy to delve into it enough to
be able to answer questions about it.

Larry Linson
Microsoft Access MVP

> Thank you for the response. Sorry, I didn't mention it earlier, I'm using
> the MS Access "Order Entry" database. There is 2 reports I've tried:
[quoted text clipped - 22 lines]
>> Larry Linson
>> Microsoft Access MVP

Reply to this Message

Duane Hookom - 28 Sep 2006 20:14 GMT

Did you read my reply? Do you understand my reply or how to create a
user-defined function?

Signature

Duane Hookom
MS Access MVP

Reply to this Message

iaqsuk - 28 Sep 2006 20:38 GMT

Hello Duane,

I'm fairly new to MS Access, so I don't understand that particular function.
It would be great if you can show me please. Thank you for your quick
response. Arnold

>Did you read my reply? Do you understand my reply or how to create a
>user-defined function?
[quoted text clipped - 4 lines]
>>> Larry Linson
>>> Microsoft Access MVP

Reply to this Message

Duane Hookom - 28 Sep 2006 22:44 GMT

Create a new standard module and add something like:
Public Function GetRange(pintDays As Integer) As String
' function that accepts a number and returns a value like
' 0-30, 31-60, 61-90, 90+
' use this in a query like:
' DaysRange: GetRange(DateDiff("d",[DateField],Date()))
'
Select Case pintDays
Case 0 To 30
GetRange = "From 0 to 30"
Case 31 To 60
GetRange = "From 31 to 60"
Case 61 To 90
GetRange = "From 61 to 90"
Case Else
GetRange = "Greater Than 90"
End Select
End Function

Save the module as "modDateFunctions".

You can then use the function in the crosstab as a column heading. If you
date ranges change, you will need to modify the function. If you want a more
flexible solution, you can set up a table with Min and Max days and the
range title.

Signature

Duane Hookom
MS Access MVP

> Hello Duane,
>
[quoted text clipped - 12 lines]
>>>> Larry Linson
>>>> Microsoft Access MVP

Reply to this Message

iaqsuk - 28 Sep 2006 23:41 GMT

Hello Duane,

Thank you for your help. I got as far as saving the module as
"modDateFunctions". I got lost with the use of the function crosstab in
column heading. Do I need to create another query with: customer, acct#, 30,
60, 90, 90+? or use the existing Sales by Customer query or report? Sorry,
I'm new here when it comes to reports. Thanks again. Arnold

>Create a new standard module and add something like:
>Public Function GetRange(pintDays As Integer) As String
[quoted text clipped - 27 lines]
>>>>> Larry Linson
>>>>> Microsoft Access MVP

Reply to this Message

Duane Hookom - 29 Sep 2006 05:06 GMT

Instead of referring to a report in some other database, why don't you
describe your data and desired report?

Signature

Duane Hookom
MS Access MVP

> Hello Duane,
>
[quoted text clipped - 38 lines]
>>>>>> Larry Linson
>>>>>> Microsoft Access MVP

Reply to this Message

iaqsuk - 30 Sep 2006 01:53 GMT

Hello Duane,
I just want to thank you for all you responses. In another forum I asked the
same question, but the question was more geared to MS Access "Order Entry"
Receivable Aging Report. This report gives aging report in 0-30, 31-60, 61-
90, 90+. This works fine, but it gives every entry for each line items and
not the totals.

I got a response to hide the Details section in that report, but I don't know
if it would of given me all the custombers with just the totals. Thank you.
Arnold

>Instead of referring to a report in some other database, why don't you
>describe your data and desired report?
[quoted text clipped - 4 lines]
>>>>>>> Larry Linson
>>>>>>> Microsoft Access MVP

Reply to this Message

Duane Hookom - 28 Sep 2006 03:33 GMT

I would suggest creating a public function that accepts a date and returns a
string value of the range name. You can then use this function as the Column
Heading for a crosstab query.

Signature

Duane Hookom
MS Access MVP

> I'm using the Default MS Access Sales by Customer reports. Is it possible
> to
> have a aging report by 0-30, 31-60, 61-90, 90+ with the total sales
> format?
>
> Can anyone help me with this report. thank you.

Reply to this Message