Can (should) an auto number be set up on a invoice (report)?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am using the reports module to create an invoice. I want the report to
generate a unique invoice number. Can this be accoumplished using an unbound
text box, or is it safer to bind the text box to a table?

The flow of transactions will be:
Multiple product lines will be pulled together (in a query) for a single
customer purchase under a single invoice number. This query will then feed
data into the invoice. I would then like to collect this unique data (in the
example, 3 product lines into 1 invoice for a customer).

It sounds like I want to use the query to append to a "sales" table (which
assigns an invoice number to the lines purchased) THEN create the invoice
based on the data in this new table. Is this the best way to go at it, though?
 
Robert,

I assume you will want to retain a record of these invoicing detals? If
so, I think it would be normal to have an Invoices table, with fields
such as:
InvoiceNumber
CustomerID
InvoiceDate
etc

.... and an ProductLines table, with fields like...
LinesID
InvoiceNumber
ProductCode
Quantity
etc

When you say "multiple product lines will be pulled together (in a
query)", it is difficult to imagine how you are doing this without some
sort of data entry to identify the customer and invoice prior to
allocating the product lines. But the concept of the table structure
above is that the InvoiceNumber field is the basis of a one-to-many
relationship between Invoices and ProductLines, allowing you to identify
which invoiced items belong to which invoice.

An incomplete answer, I know, but hopefully will help.
 
Thank you for responding.
I already have two main tables:
A "Customer" table with all of the requisite customer information
A "Products" table with product and cost

A customer can select multiple products in one order. I have created a
"Sales" query which pulls customer and product information together. The user
can then review the multiple product items via a Sales form. It is at this
point that I have created the report (invoice) that the user can preview and
print. I think this is where I have gotten ahead of myself. If I read you
right, I should output the unique combination of customer and product order
to a separate table. For example:

Customer 1 --------> Product 1 \
Customer 1 --------> Product 2 >--------Invoice 1001
Customer 1 --------> Product 3 /

The report (form) should then be based on this "Invoice" table which then
contains the incremented invoice number. Am I understanding correctly? If so,
would the best way to trigger the update be a control on the form which tells
the query to update the table?

It feels like I am close to the answer; it is teriffic to have someone(s)
review the process and send me in the right direction. Thank you.
 
Robert,

Can you explain the nature of the "Sales query"... it is difficult to
know how you are identifying the customer and the products to return the
required data from this query. Maybe posting the SQL view of the query
would help?

My suggestion was to have 2 tables, which I called in my example
Invoices and ProductLines. This is based on the fact that there is
normally a one-to-many relationship between various data elements
related to invoices. On the 'one' side is CustomerID, InvoiceDate, etc,
and then in the 'many' side is the order details. So yes, I would stick
to this idea, and store theis information in these database tables.
This is not normally an "output" process, it is a data entry process.
But if you are somehow compiling the invoice information, prior to entry
in the table, then an Append Query (or a couple of append queries) would
probably be the way to go.

But anyway, I may be able to comment more explicitly once I understand
more clearly what you are doing.
 
In hindsight, I guess what I am creating is a bit different procedurally than
the normal process...

This is being written for a service company. The "products" are procedures
that are maintained in the "products" table (product, cost).

A customer calls and makes an appointment for a procedure (or group of
procedures). The staff takes the customer information (stored in the
"Customer" table). The staff then records what procedures the customer wants
performed (this is a match of customer and procedure, which is stored in a
"schedule" table.
This table stores Customer ID, Concatenated Customer Name (separate query),
Procedure(s), Cost, Appointment Date and Appointment Time.

When the customer arrives, the staff pulls up the procedures in a "check in"
query (by last name from the concatenated field). If the staff can "upsell"
(add more procedures), the staff returns to the schedule form, and enters the
additional procedure. Once the procedures are agreed upon, the staff checks a
"sale" box.

When the procedures have been performed (check out), the staff opens a
"check out" query by again entering the last name (this query then filters by
name and by the "sale" check box checked earlier). This "check out" query has
all of the information that is required for the sale (Customer Name, Address,
Procedure(s), Cost, Sales Tax) except for the invoice number. It is this
screen where I give the staff the ability to preview/print the invoice. This
is where I am in the quandary; how do I generate the invoice number? Do I
somehow add this (invoice number) field to the Schedule table; do I create an
Invoice table and push the invoice information forward into this table (then
base the invoice on this new table)? I could see creating a command button
which makes the "check out" query an append query which updates the invoice
table, but I am not sure of how to attach the single invoice number to the
multiple product (procedure) lines for any given sale (creating the many to
one relationship).

To summarize:
Tables:
Customer
Procedure
Schedule (Invoice table not created yet)

Queries:
Name concatentation query (on customer table)
Check in query (on Schedule - filtered by Last Name)
Check out query (on Schedule - filtered by Last Name, Sale check box)

Thank you for your patience; I have learned enough about Access to get
myself in trouble. Surprisingly, this application is almost done...
 
Robert,

It seems to me that the Schedule table is the logical place for the
InvoiceNumber. If I understand you correctly, there will be an invoice
pertaining to each schedule record.

The Customer Name should not be in the Schedule table.

It is not clear how you are recording the Procedure(s) in the Schedule
table, but the Procedures relating to each Schedule should be in a
separate table, not in the Schedule table. This is a classic case of a
one-to-many relationship between Schedule and Procedures.
 
Back
Top