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

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 Robinson


"Steve Schapel" wrote:

> 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.
>
> --
> Steve Schapel, Microsoft Access MVP
>
>
> Robert Robinson wrote:
> > 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.
>
.

Quantcast