Ledger in Northwind Sample

[aneela1]

Hi All,
This is my first message on this forum. I am looking to introduce a customer ledger in the sample Northwind database. The sample from Microsoft provides only monthly, yearly and quarterlyt reports for the customers. I want the form to display all the entries that were made, meaning all the products that the customer bought as per the user defined time period, say 15 June 2014 to 12 Jan 2015, etc etc.
Please help me with this. I am working on a fresh copy of northwind sample database, no changes made.

Thank you

Regards,
Aneela

P.S: If there is a template where I can get the ledgers please help me with that please please please.

Cross Posted At:

access-programmers.co.uk/forums/showthread.php?p=1465004#post1465004

forums.tutorialized.com/sql-basics-113/ledger-in-northwind-sample-397804.html



utteraccess.com/forum/index.php?showtopic=2033453

dbforums.com/showthread.php?1707922-Ledger-in-Northwind-Sample&p=6639964

[RuralGuy]

I take it you do not wish to or do not have the skills to modify the Microsoft template?

[aneela1]

I had some skill in basic query, but that was way back in 1998, forgot it now. Please help me.

[orange]

In plain English, what is it you want to do?

I want the form to display all the entries that were made, meaning all the products that the customer bought as per the user defined time period, say 15 June 2014 to 12 Jan 2015, etc etc.

[aneela1]

when a user wants to see a customer data, he selects the dates say 1 Jan 2013 to 1 Jan 2014 and then clicks print/preview, the report should generate all entries corresponding to the customer including the dates of order and the amounts. Thank you

[orange]

Ok. Does the user know which Customer he is dealing with or does he have to search/select from a list?

A little more detail re the process you describe ---let's say we know the customer.

You would have a form, with a couple of text boxes (FromDate and To Date) and a button to invoke a report that uses the FromDate, ToDate and Customer to select and report the records. And you may have options to Print or Preview the Report.

Also, re level of detail.

If we looked at CustomerID ANTON From 1/1/1996 To 12/31/1998, this sort of result might appear

Customer ID
Order Date
TotalDollars

ANTON
27-Nov-1996
$403.20

ANTON
15-Apr-1997
$690.00

ANTON
15-Apr-1997
$191.25

ANTON
13-May-1997
$1,050.00

ANTON
13-May-1997
$184.00

ANTON
13-May-1997
$97.50

ANTON
13-May-1997
$825.00

ANTON
19-Jun-1997
$702.00

ANTON
19-Jun-1997
$560.00

ANTON
19-Jun-1997
$820.00

ANTON
22-Sep-1997
$936.90

ANTON
22-Sep-1997
$20.00

ANTON
25-Sep-1997
$75.00

ANTON
25-Sep-1997
$68.00

ANTON
25-Sep-1997
$232.50

ANTON
28-Jan-1998
$380.00

ANTON
28-Jan-1998
$280.00

based on a query such as

SELECT Customers.CustomerID, Orders.OrderDate, [UnitPrice]*[Quantity] AS TotalDollars
FROM (Customers INNER JOIN Orders ON 
Customers.CustomerID = Orders.CustomerID) INNER JOIN 
[Order Details] ON Orders.OrderID = [Order Details].OrderID
WHERE (((Customers.CustomerID)="ANTON") AND 
((Orders.OrderDate) Between [From] And [T0]));

Is that what you expect?

I realize this is a query, but I'm trying to ensure your requirements are clearly defined before jumping too deep.

[aneela1]

Thank you. Yes, Approximately, but the report should show the name of the customer once as title of the report, plus it should show the product name that was bought and for how much(which you already have done), and add the entries for that specific period at the bottom.

Thank you

Regards,
Aneela.

[orange]

aneela,

Here are some pictures :
The form by which you could select the customer and the From and To Dates:


A sample of the completed form:



A sample of the report produced: Print



And a sample of the report- preview


I can provide the related code if you wish to pursue this.

[aneela1]

Hi orange,
yes yes. this is what I want. can it also show the previous pending balance, I mean as per your example can it show the previous balance to 19 Oct 1997 as say $Amount and then adds the previous balance to the printed statement which is $187.00

Thank you.

[orange]

A few questions:

What have you done at your end?
Which version of Northwind are you using? You should post a copy of your database.
I found a copy on a site, but don't know its back ground or version.

[aneela1]

Hi orange,
to be honest, I have the northwind template sample and I just cleared the data, nothing done so far. I am using 2007 Northwind template. Thanks. I really need your help. I know I am not giving any inputs here, but I am trapped and dont know what to do. My boss needs the software ready and I do not have anything for him. I learned access 2003, but totally forgot it now. Sorry

[orange]

Post a copy of whatever database you are using.

I'm having some difficulty with the computer (speed) and versions of Northwind.
I downloaded a NWind 2000 version and the Orders/Amounts etc for LAUGB sample in earlier posts is different. The version in 2010 is different again.

So I have no idea which or what Northwind data I have or you have.

Send a copy and I'll try to get you a form. However, I caution you upfront, you're going to have to get some Access/vba skills. You cant expect to take the Northwind database, clear the tables and have it work successfully in a production environment.

No one should be running such a database without some database administration experience --version of code, software, backup/recovery, control of user names/accounts, monitoring of system/database....

[orange]

aneela1,

Attached in zip format is a copy of the Northwind database I used.

The materials that have been developed to get you a form to capture parameters.
And produce a Report as per your post, are:

Query: CustomerOrderDetailsRange
Form: CustomerOrderDetailsInDateRange
Report: rptCustomersOrdersDateRange

The Form is used to identify the Customer for whom the report is generated.
You can review the customer names and select the one you need. The CustomerID
is used to select the data for the Orders based on the FromDate and ToDate you entered
into the text boxes on the form.

The report is basic to your needs. The FromDate and ToDate are passed to the report for reference.
They are passed as OpenArgs and distilled within the Report Load event.

There are a number of debug.print statements in the code behind the form and report. These
write information to the immediate window.

A typical output of the debug is shown below: (note it is not related to the form and report shown)

Entered  OpenCustReport_Click()  15/12/2015 7:15:05 PM
Opening form :15/12/2015 7:15:05 PM
opt is 2
opnarg is 01/01/1994|12/01/1995
Report Load event  15/12/2015 7:15:47 PM
In Report_Load event***Openargs***01/01/1994|12/01/1995

Working with openArgs  15/12/2015 7:15:47 PM
Finished at 15/12/2015 7:15:47 PM

Good luck with your project.

[aneela1]

Hi orange,
Sorry to distrub you again. My access is 2007, it wasnt working at first, but now it is. Please could you code for the Northwind 2007. Sorry and thanks again.

Regards,
Aneela

[orange]

I sent you the entire Northwind that I downloaded as 2007. I only added the parts I mentioned in previous posts to create a Form and Report as discussed.
You can import the pieces I created into any version of Northwind you may have.

As I have said, you are going to have to learn some database administration, Access and vba if you are going to use the Northwind database in a production environment adapted for your business/requirements.

You really can not do hands-on database administration by means of posts on a forum(s).

For your own benefit, and that of your company/organization, I recommend you become familiar with
the 8 videos in this series by Dr.Daniel Soper to learn more about database, normalization, relationships and modeling.

Also, this series by Steve Bishop is one of the best on Access and programming. It says 2013, but is applicable
generally to earlier versions.

Good luck.