Ledger in Northwind Sample

[aneela1]

Hi orange,
Thank you. I tried to copy the files to the 2007 version and tried changing the fields of the tables in the query and form, but it does not seem to work. the fields are too different and mismatch. I have tried it. For example in 2003 it was Company Name, whereas it is Company only in 2007. there are loads of other items, some tables have Product Code as field name in Design View, but it has Product ID on the table view, etc, I tried to match them, but could not. Please if you could only change the field names in the query that you had posted. Bundle of thanks.

Regards,


Aneela

[aneela1]

Please find attached my version of Northwind 2007. Thank you.

[orange]

aneela,
I have made a copy of the database you sent. The 2007 version of Northwind has different field names than the 2000 version I was using.
I have modified the query, form and report that I had shown in previous posts to use the field and table names in the 2007 Northwind.
I found that there were no Orders in the version of 2007 that you sent. I added some Orders for Company A such that I could test the query, form and report. The 2007 version requires that you follow a process to create a Customer Order, submit, complete etc. I was not familiar with the process but managed to get a few records for Company A into the Orders table.

The Report parameters are entered via the Form where you select Company,FromDate, ToDate and Print option. This info is saved in the query

The base query SQL (CustOrderDetailsRange_2007) is

SELECT Customers.ID
    ,Orders.[Order ID]
    ,Orders.[Order Date]
    ,Products.[Product Name]
    ,Customers.Company
    ,[Order Details].Quantity
    ,[Order Details].[Unit Price]
    ,([Order Details].[Quantity] * [Order Details].[Unit Price]) AS TotalPrice
FROM Products
INNER JOIN (
    (
        Customers INNER JOIN Orders ON Customers.ID = Orders.[Customer ID]
        ) INNER JOIN [Order Details] ON Orders.[Order ID] = [Order Details].[Order ID]
    ) ON Products.ID = [Order Details].[Product ID]
WHERE Customers.Id = 1
    AND Orders.[Order Date] BETWEEN #10 / 12 / 2015 #
        AND #25 / 12 / 2015 #;

The WHERE criteria section is filled in based on your selections on the Form. This query(CustOrderDetailsRange_2007) is the record source of the Report.

In addition I have included a module DataDictionary that contains a function DocumentTables. This function reads all non system tables and records info in a table called data_dictionary. The table is rebuilt each time the DocumentTables function is executed. This was helpful when I had to see/use the new field names.

Here is a sample of the data_dictionary info:

EntryID
table_name
table_description
field_name
field_description
ordinal_position
data_type
length
default

1
Customers


ID


0
Long
4



2
Customers


Company


1
Text
50



3
Customers


Last Name


2
Text
50



4
Customers


First Name


3
Text
50



5
Customers


E-mail Address


4
Text
50



6
Customers


Job Title


5
Text
50



7
Customers


Business Phone


6
Text
25



8
Customers


Home Phone


7
Text
25



9
Customers


Mobile Phone


8
Text
25



10
Customers


Fax Number


9
Text
25



11
Customers


Address


10
Memo
0



12
Customers


City


11
Text
50



13
Customers


State/Province


12
Text
50



14
Customers


ZIP/Postal Code


13
Text
15



15
Customers


Country/Region


14
Text
50



16
Customers


Web Page


15
Memo
0

Here is a picture of the Form (CustomerOrderDetailsInRange_2007)

Here is a picture of the associated report (CustomersOrdersDateRange)


I also added a table called tblChanges to identify changes that I made when setting up the query, form and report for the 2007 Northwind database.
Here is a graphic of the table contents.



I am attaching (zip format) the 2007 database you posted and the latest changes I have made.

As mentioned previously, you are going to have to learn (or hire someone with) database skills - database admin, design, operations, .... To take a demo database (especially the 2007 northwind) and adapt it for your operational/production database is not trivial. You really need to map out and plan your activities. The video tutorials mentioned previously are a good start.

I wish you good luck with your project. And a Merry Christmas and Happy New Year.

[aneela1]

Hi Orange,

I am looking for something like this. Please help and bundle of thanks for what you have already done.

Attachment 23189

[aneela1]

Dear orange,
I am stuck. My boss gave me the picture of how he wants the ledger to be(Post # 33), that is the customer report that you have prepared. Please help me. I beg you. This is the last time I am disturbing you. I know its too much to ask from you again and again, but I am sorry, I tried but failed to make the amendments in the form to make the payment information as well as this format. Please Please Please, I beg you to help me.

I hope you understand my position.

Thank you

Regards,
Aneela

[RuralGuy]

Dear aneela1:
You have chosen to turn this sample from Microsoft into an accounting system and those features are not built into the sample. There is no ledger in the system nor any way to receive a payment. This sample is a company that issues Purchase Orders and receives inventory. What you are asking of Orange is simply NOT FAIR. His comments at the end of Post #32 are simply the facts. I wish you well too but your requirements are well beyond anything these type of Forums can provide. Orange may think differently and wish to voice his opinion again but I believe he will agree with me. Sorry!

[aneela1]

Perhaps I could simply request Orange to help me with a payment form and then add this to the report that he has already prepared in post #18 meaning that the report to have the payment details also. Please, begging here. Regards,
Thank you, Aneela

[June7]

This goes way beyond just building a form. Where do you want the payment data to go? This involves a whole new dimension to the database structure. Need to identify new data entities (what is an 'account', payment of what - from customers or to vendors or both) and relationships, build new table(s), queries, forms, reports, code and integrate with existing structure. Calculations needed for 'balance' are not always straightforward, especially on a form. Orange and RuralGuy have already advised that a user forum is not best vehicle in which to pursue assistance for this effort and I concur. We often provide solutions to specific and focused issues but sometimes can only offer guidance for direction to pursue on broader scopes. You have received both here.

What you want is an accounting system (your 'ledger'). Why reinvent the wheel? You might consider OTS package like QuickBooks. Cheaper than the cost of your time to build system. However, I don't know if it is appropriate for multi-user environment. I worked with single-user version but I suppose they have a multi-user package and licensing.

Or this https://www.microsoftaccessexpert.co...-Database.aspx

Maybe your boss should read this thread.

[RuralGuy]

You know there are some pretty inexpensive accounting systems done in Access:
https://www.microsoftaccessexpert.com/Microsoft-Access-Basic-Business-Accounting-Database.aspx

[RuralGuy]

Looks like June and I found the same link.

[orange]

aneela1,

What you are asking really is for someone to build an application for you. That is not going to happen here.
Before you or anyone can build an application, they need documented requirements. Anyone trying to help you solve "the issue(s)" will need to thoroughly understand the underlying business issues/concepts and opportunities.

As I said previously, and as others have emphasized to you, you can not take the Northwind demo database (or any other for that matter) and simply convert one or two forms and magically create an operational accounting system.

As I see things, you and your boss need to hire a contractor/consultant who can listen to your "concerns" and layout a plan of the steps required to build or buy an applicable application. Quickbooks and others have been suggested, and that would be a good place to start some analysis to determine the feasibility of an OTS product. But to do this you still need a documented set of requirements. Database is only one piece of an accounting system. However, it doesn't appear that you have documented your requirements, nor done any detailed analysis and/or design.

The problem before you is not going to be resolved by comments and posts on a forum. The forum is here to guide, advise, recommend via responses to focused questions.

Good luck with your decisions and project.