Default Sales by Customer MS Access Report

  • Thread starter Thread starter iaqsuk via AccessMonster.com
  • Start date Start date
I

iaqsuk via AccessMonster.com

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.
 
iaqsuk via AccessMonster.com said:
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?
Click to expand...

_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
 
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.
 
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
 
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
 
Did you read my reply? Do you understand my reply or how to create a
user-defined function?
 
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

Duane said:
Did you read my reply? Do you understand my reply or how to create a
user-defined function?
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:
Click to expand...
[quoted text clipped - 22 lines]
Click to expand...
 
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.

--
Duane Hookom
MS Access MVP

iaqsuk via AccessMonster.com said:
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

Duane said:
Did you read my reply? Do you understand my reply or how to create a
user-defined function?
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:
Click to expand...
[quoted text clipped - 22 lines]
Larry Linson
Microsoft Access MVP
Click to expand...
Click to expand...
Click to expand...
 
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


Duane said:
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.
Hello Duane,
Click to expand...
[quoted text clipped - 12 lines]
Click to expand...
 
Instead of referring to a report in some other database, why don't you
describe your data and desired report?


--
Duane Hookom
MS Access MVP

iaqsuk via AccessMonster.com said:
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


Duane said:
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.
Hello Duane,
Click to expand...
[quoted text clipped - 12 lines]
Larry Linson
Microsoft Access MVP
Click to expand...
Click to expand...
Click to expand...
 
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



Duane said:
Instead of referring to a report in some other database, why don't you
describe your data and desired report?
Hello Duane,
Click to expand...
[quoted text clipped - 38 lines]
Click to expand...
 
Back
Top