473,320 Members | 2,088 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

creating invoices for all customers

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
4 5093
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
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

6
by: Dim St Thomas | last post by:
I am a developer working on a database client program. I am testing this program on a Windows XP machine (1.5 GHz AMD chip, 480 Mb RAM, 60 Gb disk) This machine has Oracle 9.2.0.1.0 and RedBrick...
2
by: tom | last post by:
Hi, I have built an Access DB for a child care business. Family table stores the Family info. Room the different room names, capacity etc. Price stores the various price codes and amounts. The...
9
by: StevenH | last post by:
I stumped Can someone assist me with the query for "invoices from 2002 & not from 2004" thanks
5
by: Mike Turco | last post by:
What is the difference between creating relationships in the front-end vs. the back-end database? I was trying to create a relationship in a database front-end and noticed that I could not check...
1
by: Shelby | last post by:
Problem: My company generates its own data export from a propietary database. These (free) tables can be read in C#.NET using a Visual FoxPro driver (vfpoledb). I can read each of the six tables...
3
by: Comboni | last post by:
Creating invoices from individual Access records. I think that my question is simple, but as a newcomer to using MS Access 2003, it is beyond me. I have read through the various ‘Invoice’ inputs...
6
by: lawpoop | last post by:
Hello! I am working on a map of a rather large php project that I've been working on. I hope to create a map of the files of the project that would look like the output of the unix 'tree'...
2
by: Florian | last post by:
hey i am new in php and my thesis of the university consist in making a software for an inventory. I decided to use php and my sql for doing this program but i'm having problems with the invoices....
9
by: ARC | last post by:
In case anyone has ran into this yet. The following code used to work with older versions of the MS Outlook library, but would error out in 2007: Dim objOutlook As Outlook.Application Dim...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.