Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Simple Sales Database

Posted on 2004-10-02
6
Medium Priority
?
2,670 Views
Last Modified: 2010-08-05
I am trying to keep track of my invoices and need to create an access database. The idea is quite simple though i cannot get this to work.

All the orders come in at different times in the week though the order can be from the same person (i.e. they could order on monday and then order on thursday)

This is straight forward one form that does this for me

Next at the end of every week i need to do a query/report that sorts all the orders taken and groups them by customer name and this is  then the invoice (the report is easy though i need to put an invoice number on it this is the wierd part)

At the end of the month i need to accumulate all the invoices for each customer and put them on a statement (invoices from the past 30/31 days)

I have been trying this for ages and cannot get this to work. Does anyone have any ideas or any sample databases to start me off

Regards

Systemworx
0
Comment
Question by:Systemworx
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
6 Comments
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 12209592
Have you looked at the Northwind sample database that comes with Access?
0
 

Author Comment

by:Systemworx
ID: 12211222
Yes i have had a look at the database but each time i start to customise it, it start to mess up

I have done it so that its similar to what i want but i cannot get the statement bit done

Its easier to start a fresh

I just need all the orders to accumulate into a query and give those orders an Invoice number and then all the invoices for a month to accumulate and give this a statement number
0
 
LVL 10

Accepted Solution

by:
calpurnia earned 800 total points
ID: 12215723
Have you considered coming at this from a different angle?

Start with 3 tables:

tblInvoices (with fields InvoiceID, CustomerID, DateInvoiceCreated, DateInvoiceSent)tblCustomers (with fields CustomerID, CustomerName etc.)
tblOrders (with fields  InvoiceID, OrderDate,OrderDetails...)

where tblCustomers is related to tblInvoices on CustomerID, and tblInvoices is related to tblOrders on InvoiceID. Create a query qryOpenInvoices based on tblInvoices which selects those invoices for which DateInvoiceSent is Null.

Make InvoiceID in tblInvoices an Autonumber field.

Create a form that allows you pick a customer from a list, checks to see whether there's a record in qryOpenInvoices matching that CustomerID, and if not, creates a new invoice record. Then open another form based on the invoice record, with a subform showing the rows from tblOrders.

Hope this makes sense.

Karen


0
 
LVL 10

Expert Comment

by:calpurnia
ID: 12443271
Hi Systemworx, how are you getting on with your invoices?
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question