Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2672
  • Last Modified:

Simple Sales Database

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
Systemworx
Asked:
Systemworx
  • 2
1 Solution
 
shanesuebsahakarnCommented:
Have you looked at the Northwind sample database that comes with Access?
0
 
SystemworxAuthor Commented:
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
 
calpurniaCommented:
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
 
calpurniaCommented:
Hi Systemworx, how are you getting on with your invoices?
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now