By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,107 Members | 1,318 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,107 IT Pros & Developers. It's quick & easy.

creating invoices for all customers

P: n/a
Thanks to much help from everyone in my previous thread, I've made it a
pretty fair ways into my billing/invoicing db.

I'm now needing a way to cycle through all the records in a table and create
invoice records for them in another table, but I have no idea where to
begin. Here's the relevant tables:

==============
Table: Customer_tbl

Columns:
----------
id (PK)
<typical contact info>
----------------
Table: Invoice_tbl

Columns
---------
Id (PK)
Customer_tbl_ID (FK)
Date

Relationships
-------------
Customer_tbl.id <--> Invoice_tbl.Customer_tbl_ID
Relationship Type: One-To-Many

Invoice_tbl.id <--> InvoiceItems_tbl.Invoice_tbl_ID
RelationshipType: One-To-Many

------------
Table: InvoiceItems_tbl

Columns
---------
id (PK)
Invoice_tbl_ID (FK)
Discription
Amount
DiscountRate

Relationships
-------------
Invoice_tbl.id <--> InvoiceItems_tbl.Invoice_tbl_ID
RelationshipType: One-To-Many

===========
If someone has a few minutes to help me with this and maybe throw me a few
lines of psudo-code I'd really appreciate it.

TIA
-jeremy
Nov 12 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
Two things ---
1. Invoices are created each time an order is received. Why do you want to
cycle through your customers table and create an invoice for each?

2. You missing a Products table.
TblProduct
ProductID
Description
Price
DiscountRate
Inventory
ReorderPoint
NoLongerAvailableForSale (Yes/No)

Then your InvoiceItems table should be

TblInvoiceItem
InvoiceItemID
InvoiceID
ProductID
Quantity
Price

Two suggestions:
1. For the PK of each table use the tablename + ID (See above tables)
2. Your database will be easier to read if you don't use spaces or underlines
in names.
--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
www.pcdatasheet.com
"Jeremy Weiss" <jw*****@hotmail.com> wrote in message
news:vo************@corp.supernews.com...
Thanks to much help from everyone in my previous thread, I've made it a
pretty fair ways into my billing/invoicing db.

I'm now needing a way to cycle through all the records in a table and create
invoice records for them in another table, but I have no idea where to
begin. Here's the relevant tables:

==============
Table: Customer_tbl

Columns:
----------
id (PK)
<typical contact info>
----------------
Table: Invoice_tbl

Columns
---------
Id (PK)
Customer_tbl_ID (FK)
Date

Relationships
-------------
Customer_tbl.id <--> Invoice_tbl.Customer_tbl_ID
Relationship Type: One-To-Many

Invoice_tbl.id <--> InvoiceItems_tbl.Invoice_tbl_ID
RelationshipType: One-To-Many

------------
Table: InvoiceItems_tbl

Columns
---------
id (PK)
Invoice_tbl_ID (FK)
Discription
Amount
DiscountRate

Relationships
-------------
Invoice_tbl.id <--> InvoiceItems_tbl.Invoice_tbl_ID
RelationshipType: One-To-Many

===========
If someone has a few minutes to help me with this and maybe throw me a few
lines of psudo-code I'd really appreciate it.

TIA
-jeremy

Nov 12 '05 #2

P: n/a
The company that I'm putting this together for is a services company that
charges their customers a monthly fee. I'm needing something to generate the
usual monthly invoice for all 1500 of their customers. They only have one
service, so the description/product will be the same each month, thus no
need for a product table.

I'm just wanting a button they can click to 'bill' their customers. When
they click the button I want Access to read the baserate value from one
table and create an invoice in the invoice_tbl and a line item in the
invoicedetail_tbl using that baserate value as the amount, then go to the
next customer and do it over.

I did leave out a table though, just not the one you were expecting.

Table: CustomerAccount_tbl
- id (PK & FK Customer_tbl)
- baserate
- accountbalance

The problem is, I have no idea where to start, so if anyone has a few
minutes to explain to me whether I use a module or macro or whatever and
maybe throw in some psudocode I'd really appreciate it.

TIA,
-jeremy

P.S. Thanks for the naming convention suggestions

----- Original Message -----
From: "PC Datasheet" <sp**@bellsouth.net>
Newsgroups: comp.databases.ms-access
Sent: Thursday, October 09, 2003 12:22 AM
Subject: Re: creating invoices for all customers

Two things ---
1. Invoices are created each time an order is received. Why do you want to cycle through your customers table and create an invoice for each?

2. You missing a Products table.
TblProduct
ProductID
Description
Price
DiscountRate
Inventory
ReorderPoint
NoLongerAvailableForSale (Yes/No)

Then your InvoiceItems table should be

TblInvoiceItem
InvoiceItemID
InvoiceID
ProductID
Quantity
Price

Two suggestions:
1. For the PK of each table use the tablename + ID (See above tables)
2. Your database will be easier to read if you don't use spaces or underlines in names.

Nov 12 '05 #3

P: n/a
Since there is always only one invoice item per invoice, you can consolidate
your Invoice table and invoice item tables into one Invoice table that looks
like:

TblInvoice
InvoiceID
InvoiceDate
CustomerID
InvoiceAmount

Your Customer Account table needs revised to:
TblCustomerAcct
CustomerAcctID
CustomerID
BaseRate
AccountBalance

1. Build a query named CreateInvoices based on TblCustomerAcct and include the
fields CustomerID and BaseRate. Convert the query to an append query to append
to TblInvoice. Access will know to append CustomerID to CustomerID but you will
have to tell the query to append baserate to InvoiceAmount.
2. Build an unbound popup form named NewInvoiceDate with a single textbox named
InvoiceDate and labelled "Enter Invoice Date".
3. Build a second query named UpdateInvoiceDate based on TblInvoice and only
include the field InvoiceDate. Make the criteria for the field Is Null. Convert
the query to an Update query and where it says update to put:
Forms!NewInvoiceDate!InvoiceDate
4. Put this code in the AfterUpdate event of InvoiceDate on your popup form:
DoCmd.SetWarnings False
DoCmd.OpenQuery "CreateInvoices"
DoCmd.OpenQuery "UpdateInvoiceDate"
DoCmd.SetWarnings True

When the code runs, it first appends 1500 new records to TblInvoice, one record
for each customer. The InvoiceDate field for all 1500 records will be empty
after the append. The second query then updates the blank InvoiceDate field of
the 1500 records to the date you entered on the popup form.
--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
www.pcdatasheet.com

"Jeremy Weiss" <jw*****@hotmail.com> wrote in message
news:vo************@corp.supernews.com...
The company that I'm putting this together for is a services company that
charges their customers a monthly fee. I'm needing something to generate the
usual monthly invoice for all 1500 of their customers. They only have one
service, so the description/product will be the same each month, thus no
need for a product table.

I'm just wanting a button they can click to 'bill' their customers. When
they click the button I want Access to read the baserate value from one
table and create an invoice in the invoice_tbl and a line item in the
invoicedetail_tbl using that baserate value as the amount, then go to the
next customer and do it over.

I did leave out a table though, just not the one you were expecting.

Table: CustomerAccount_tbl
- id (PK & FK Customer_tbl)
- baserate
- accountbalance

The problem is, I have no idea where to start, so if anyone has a few
minutes to explain to me whether I use a module or macro or whatever and
maybe throw in some psudocode I'd really appreciate it.

TIA,
-jeremy

P.S. Thanks for the naming convention suggestions

----- Original Message -----
From: "PC Datasheet" <sp**@bellsouth.net>
Newsgroups: comp.databases.ms-access
Sent: Thursday, October 09, 2003 12:22 AM
Subject: Re: creating invoices for all customers

Two things ---
1. Invoices are created each time an order is received. Why do you want

to
cycle through your customers table and create an invoice for each?

2. You missing a Products table.
TblProduct
ProductID
Description
Price
DiscountRate
Inventory
ReorderPoint
NoLongerAvailableForSale (Yes/No)

Then your InvoiceItems table should be

TblInvoiceItem
InvoiceItemID
InvoiceID
ProductID
Quantity
Price

Two suggestions:
1. For the PK of each table use the tablename + ID (See above tables)
2. Your database will be easier to read if you don't use spaces or

underlines
in names.


Nov 12 '05 #4

P: n/a
Thanks for the help... off to put it into action.

-jeremy

"PC Datasheet" <sp**@bellsouth.net> wrote in message
news:Uk*****************@newsread2.news.atl.earthl ink.net...
Since there is always only one invoice item per invoice, you can consolidate your Invoice table and invoice item tables into one Invoice table that looks like:

TblInvoice
InvoiceID
InvoiceDate
CustomerID
InvoiceAmount

Your Customer Account table needs revised to:
TblCustomerAcct
CustomerAcctID
CustomerID
BaseRate
AccountBalance

1. Build a query named CreateInvoices based on TblCustomerAcct and include the fields CustomerID and BaseRate. Convert the query to an append query to append to TblInvoice. Access will know to append CustomerID to CustomerID but you will have to tell the query to append baserate to InvoiceAmount.
2. Build an unbound popup form named NewInvoiceDate with a single textbox named InvoiceDate and labelled "Enter Invoice Date".
3. Build a second query named UpdateInvoiceDate based on TblInvoice and only include the field InvoiceDate. Make the criteria for the field Is Null. Convert the query to an Update query and where it says update to put:
Forms!NewInvoiceDate!InvoiceDate
4. Put this code in the AfterUpdate event of InvoiceDate on your popup form: DoCmd.SetWarnings False
DoCmd.OpenQuery "CreateInvoices"
DoCmd.OpenQuery "UpdateInvoiceDate"
DoCmd.SetWarnings True

When the code runs, it first appends 1500 new records to TblInvoice, one record for each customer. The InvoiceDate field for all 1500 records will be empty after the append. The second query then updates the blank InvoiceDate field of the 1500 records to the date you entered on the popup form.
--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
www.pcdatasheet.com

"Jeremy Weiss" <jw*****@hotmail.com> wrote in message
news:vo************@corp.supernews.com...
The company that I'm putting this together for is a services company that charges their customers a monthly fee. I'm needing something to generate the usual monthly invoice for all 1500 of their customers. They only have one service, so the description/product will be the same each month, thus no
need for a product table.

I'm just wanting a button they can click to 'bill' their customers. When
they click the button I want Access to read the baserate value from one
table and create an invoice in the invoice_tbl and a line item in the
invoicedetail_tbl using that baserate value as the amount, then go to the next customer and do it over.

I did leave out a table though, just not the one you were expecting.

Table: CustomerAccount_tbl
- id (PK & FK Customer_tbl)
- baserate
- accountbalance

The problem is, I have no idea where to start, so if anyone has a few
minutes to explain to me whether I use a module or macro or whatever and
maybe throw in some psudocode I'd really appreciate it.

TIA,
-jeremy

P.S. Thanks for the naming convention suggestions

----- Original Message -----
From: "PC Datasheet" <sp**@bellsouth.net>
Newsgroups: comp.databases.ms-access
Sent: Thursday, October 09, 2003 12:22 AM
Subject: Re: creating invoices for all customers

Two things ---
1. Invoices are created each time an order is received. Why do you
want to
cycle through your customers table and create an invoice for each?

2. You missing a Products table.
TblProduct
ProductID
Description
Price
DiscountRate
Inventory
ReorderPoint
NoLongerAvailableForSale (Yes/No)

Then your InvoiceItems table should be

TblInvoiceItem
InvoiceItemID
InvoiceID
ProductID
Quantity
Price

Two suggestions:
1. For the PK of each table use the tablename + ID (See above tables)
2. Your database will be easier to read if you don't use spaces or

underlines
in names.



Nov 12 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.