Re: 2nd try: have default items on every report
- From: "BruceM" <bamoob@xxxxxxxxxxxxxxxxx>
- Date: Thu, 28 Dec 2006 11:35:15 -0500
You should have a Customer table. Also, each invoice may contain many line
items (which means a LineItems table), and each plant may be associated with
many invoices and many customers. It may look something like this:
tblCustomer
CustID (PK)
CustName
Address
City
other fields for information specific to the customer
tblInvoice
InvoiceID (PK)
CustID (FK)
InvoiceDate
other fields with information specific to the order (e.g. payment terms,
delivery instructions)
tblLineItems
LineItemID (PK)
InvoiceID (FK)
ProductID (FK)
Price
Quantity
tblProduct
ProductID
ProdDescription
UnitPrice
etc.
Create relationships between the PK fields and their namesake FK (foreign
key) fields in other tables. Then, here is one approach to handling your
situation: Create a form (frmInvoice) based on tblInvoice, and a subform
(fsubLineItems) based on tblLineItems. frmInvoice can contain a combo box
that uses as its row source a query that includes CustID and CustName from
tblCustomer. The combo box column count is 2, its bound column is 1, and
its column widths are something like 0";1.5". The wizard can help with
that.
The subform would be a continuous form including a combo box based on
tblProducts. Again, view the product (plant) description in the combo box
drop-down list, but store the ProductID.
The Northwinds sample database that ships with Access will give you some
more ideas about how to handle this.
As for printing the complete product listing on every invoice, I don't see
why you want to make your customers sort through a listing of 60 items when
they have bought only one, but that's your business choice. I would prefer
to make it as simple as possible for the customer. If a last-minute request
is made, presumably it happens before the truck leaves the nursery/home
office, in which case it may be simpler than you think to add a new item or
two and print a new invoice. But if late additions to an order can only be
recorded longhand, you could add a couple of lines to the invoice for such a
purpose, and maybe include on the invoice a listing based on tblProducts so
that you can have your complete product list on each order. If the idea is,
say, to have four columns of fifteen items each, and a space next to each
item for quantity and price, you would have to devise a way to loop through
the items that were actually ordered, and to insert appropriate values next
to the items on the master product list that appears on each invoice. Oh,
and hope that you don't add a new product, which would mean you need to
redesign the invoice.
The only other thing that occurs to me is to see if you can adapt something
like Duane Hookom's At Your Survey, which may be found here:
http://www.rogersaccesslibrary.com/OtherLibraries.asp
Bottom line, unless I am missing something, is that you are in for a
complicated situation if you need to print out the full product listing,
with Qty and Price next to the items that were ordered and the other items
left blank. Maybe you should use a blank form, fill it in by hand, and do
the data entry later, if revising the order must be performed longhand
anyhow. It sounds as if last-minute handwritten additions are a common
occurence, so using the database for after the fact data entry may be the
simplest solution.
"JD" <JD@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:345DC60C-6070-433A-BAD9-83ABC8B7EECA@xxxxxxxxxxxxxxxx
I will try and respond to both of yours here. Here's the real world
situation. We are selling plants to customers. Orders are printed up by
date range on delivery date. On every order report, our managers want to
list 60 default plants so that if last minute request are made the
delivery
person can write them on the form and later be put in the database. I
have
the plantDescriptions pulled from the PlantList Table. and there is a 1
to
many relationship between the Customer Info Table and the Orders Table.
Customer Info Table
TrackingNumber <primary Key>
Name <text>
Address<text>
Orders
LineNumber <Primary Key>
TrackingNumber <int>
PlantDescription <text>
Qty <int>
DelDate <date>
PlantList
PlantDescription <text>
I have a form created
--
JD
"BruceM" wrote:
I forgot about wanting 60 items on every invoice whether or not the item
is
sold. You could probably do that with labels for each item, and VBA code
to
display values in unbound text boxes next to the labels, or something
like
that, but the point of listing all 60 items on every order (if that is
the
situation) is lost on me.
"JD" <JD@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:2600007A-B1E1-45CF-808B-B4B92DA01FB4@xxxxxxxxxxxxxxxx
Sorry, I am still getting the hang of formatting the text correctly.
Here
is
what the tables look like:
Table1
TrackingNumber <primary key>
Name <text>
Address <text>
Table2
TrackingNumber
ItemDescription <text>
Qty <smallint>
LineNumber <Primary Key>
Table3
ItemDescription <text>
--
JD
"JD" wrote:
I am sorry I was extremely confusing. Let me try this again.
I have three tables as listed below:
Table1 Table2
Table3
TrackingNumber <primary key> TrackingNumber <int>
ItemDescription
Name <text> ItemDescription <text>
Address <text> Qty <smallint>
LineNumber <Primary Key>
What we have are reports that are printed for every order in a
specified
date range. They want the default 60 items printing on every report
even
if
there are no quantities. If there are quantities, they would like
these
numbers printed on the report as well and have them recorded in
Table2.
I am
really not sure how to accomplish this without creating a lot of data
that
takes up space.
Please let me know if I need to give more info.
Thanks for you help and patience.
--
JD
.
- Follow-Ups:
- References:
- Prev by Date: Re: Update Second Table from Form
- Next by Date: Re: Update Second Table from Form
- Previous by thread: Re: 2nd try: have default items on every report
- Next by thread: Re: 2nd try: have default items on every report
- Index(es):
Relevant Pages
|
