2nd try: have default items on every report

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

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

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

Without knowing the real-world situation you are trying to address it is
difficult to offer specific suggestions. My best guess is that this is some
sort of invoice system, and that TrackingNumber is the invoice ID. If so,
to whom are the items being sold? Storing personal information in the
invoice table makes sense if either each customer interacts with the system
once only (i.e. no repeat customers), or if you need to store information
that was current at the time of the order (such as Address, perhaps, if you
need to be able to confirm the address to which an order was sent), but in
most cases you would link to that information rather than storing it. By
the way, Name is a reserved word, and should not be used for a field name;
and Name and Address should be split into FirstName, LastName, Street, City,
etc. fields.
Table2 seems to be line items on the invoice (?), and Table 3 seems to be a
Products table. If so, you should link to the Products table; you would
store ProductID and probably Price in the LineItems table, along with
Quantity, as you have done.
I won't go too far down this road on guesswork, but if you describe just
what you need to do it will probably be possible to come up with some
specific ideas.

"JD" wrote in message
...
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

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" wrote in message
...
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

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
Addresstext

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" wrote in message
...
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

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 he
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" wrote in message
...
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
Addresstext

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" wrote in message
...
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

Thank you. I will begin making some changes to reflect your suggestions.

--
JD


"BruceM" wrote:

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 he
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" wrote in message
...
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
Addresstext

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" wrote in message
...
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