Form Design Flaw update Order Table

[lmv]

Sorry for the complicated disjointed question...
This is for a construction company that has categories for every area of
building a house.
25 Main Categories /166 Sub Categories
350 Suppliers
table of 2000 products =PRODUCTSTTL

I have a products list with normal product fields. I have a frmOdersList
that has QTYneeded and a check box - check box is triggered by the entry of
info into the QTYneed box. (Right now these are bound to the ProductTTL table)
(That was so I could sort by the checked box) BUT... now the design seems
flawed as I don't need the QTYneeded and the ck box on the PRODUCTSTTL table
at all. I need them and all the detail from the frmOrdersList based on
PRODUCTSTTL table to be added to the ORDER and ORDER DETAILS tables...

I have a categories form based on Categories Table that uses the PRODUCTSTTL
table as a sub and sorts items by the categories. (So you only see the list
of items that are in that category) This works well. If you have a new
product to add to a category it is fine it adds it to the PRODUCTSTTL table.

What I need to know is... without having redundant data... I like my "idea"
of being able to just add a qtyneeded and it automatically adds ALL of the
product data to the ORDER table including the qtyneeded I just don't know HOW
to do it? I know it is probably with a query...

I hope this is clear...
Thanks

 

[tina]

without having redundant data... I like my "idea"

of being able to just add a qtyneeded and it automatically adds ALL of the
product data to the ORDER table including the qtyneeded

it's not clear what you mean here. if you're *literally* storing product
data in the orders table, that is duplicating data. if all the product data
is stored in the products table, then all you need to store about a product,
in the orders table, is the product record's primary key value. quantity
should be in the orders table, of course, because that data is not
descriptive of the product, it's descriptive of the specific *order* of that
product.

if you want to *display* product data in the orders *form*, there are
several ways to do it. you can use a combo box to choose the product, and
include that additional fields in the combo box's RowSource; then reference
the appropriate combo box columns in one or more unbound textboxes, to
display the data. or you could use a DLook() function to retrieve each field
you want to display (i wouldn't use this solution if you have more than a
few fields to retrieve - not unless a combo box, or VBA code, is not an
option). or you could open a recordset in VBA to retrieve the fields, and
assign the values to one or more unbound textbox controls.

hth

 

[Larry Daugherty]

I don't understand all of your post so I'll just respond to the parts
I think I understand.

You should be able to remove the Boolean field from your table and
it's checkbox from the form.

You absolutely, positively don't want to copy data from one table into
a record in another table. You simply need to RELATE the tables to
each other in a one-to-many relationship. That is most easily done by
including the Primary Key of the one side table as the Foreign Key of
the many side table and then, in the Relationships window, draw a
relationship and establish Referential Integrity.

HTH

 

[lmv]

tina,
Thanks for the response...

it's not clear what you mean here. if you're *literally* storing product
data in the orders table, that is duplicating data.

You're right all I need to "store" is the qtyneeded & a Nunitprice (new)
All other info should be lookup...
So you are saying my Order Details Tbl just needs ProductID though my form
should be linked with a query to the productTTL the Order and Order Detail...
I don't want a combo because I want to see ALL that categories products
from the products tbl which will be sorted by category automatically when the
person selects what category (cbo to the categories tbl) They will see it in
the subfrm in my orderfrm.

So, I have a form based on a qry with alot of glitches at this point
following

SELECT ProductsTTL.*, Orders.OrderID, Orders.ProjectID, Orders.CategoryID,
Orders.Department, [Order Details].NUnitPrice, [Order Details].Quantity,
Orders.PurchaserID, Orders.OrderDate, Orders.RequiredDate, Orders.Description
FROM ProductsTTL LEFT JOIN (Orders RIGHT JOIN [Order Details] ON
Orders.OrderID = [Order Details].OrderID) ON ProductsTTL.ProductID = [Order
Details].ProductID;
(All this inner join right and left I don't understand)
BUT... the question still is if the person adds qtyneeded to the box on the
qryfrm how does all of the data get saved in the orders and orders detail
tbls? And can I have the check box so that they can see that they ordered an
item on the list without it being bound to the ProductsTTL table?

option). or you could open a recordset in VBA to retrieve the fields, and
assign the values to one or more unbound textbox controls.

I don't know how to do this...

I am sorry that I can't make this more clear... I am so new and conceptually
I know what I want to do but I don't have the skill yet to accomplish it. I
know the order detail should be able to be viewed by the form I design even
if it is just numbers etc in the table and that I should be able to
manipulate it for reports/invoices etc. But, I am researching/reading looking
at others examples until my eyes blur and then I still don't know what step
to start with. And now half of my forms don't work because the qry is wrong...

 

[tina]

i'm having a hard time getting a clear picture of your setup from the posted
descriptions. maybe we better back up a bit, and see what your
tables/relationships setup is. please post details of your design in the
following format, as

TableName
FieldName (primary key, data type)
AnotherFieldName
AnotherFieldName

AnotherTableName
FieldName (primary key, data type)
AnotherFieldName (foreign key from TableName, data type)
AnotherFieldName

TableName.FieldName 1:n AnotherTableName.AnotherFieldName
the previous line describes the relationship between the two tables as 1 to
many, and the primary/foreign key fields that the tables are linked on.

if you're not familiar with the terms "primary key" and "foreign key" in the
context of Access table relationships, then you need to read up on data
normalization/relationships. see
http://home.att.net/~california.db/tips.html#aTip1 for more information.

hth

tina,
Thanks for the response...

it's not clear what you mean here. if you're *literally* storing product
data in the orders table, that is duplicating data.

You're right all I need to "store" is the qtyneeded & a Nunitprice (new)
All other info should be lookup...
So you are saying my Order Details Tbl just needs ProductID though my form
should be linked with a query to the productTTL the Order and Order Detail...
I don't want a combo because I want to see ALL that categories products
from the products tbl which will be sorted by category automatically when the
person selects what category (cbo to the categories tbl) They will see it in
the subfrm in my orderfrm.

So, I have a form based on a qry with alot of glitches at this point
following

SELECT ProductsTTL.*, Orders.OrderID, Orders.ProjectID, Orders.CategoryID,
Orders.Department, [Order Details].NUnitPrice, [Order Details].Quantity,
Orders.PurchaserID, Orders.OrderDate, Orders.RequiredDate, Orders.Description
FROM ProductsTTL LEFT JOIN (Orders RIGHT JOIN [Order Details] ON
Orders.OrderID = [Order Details].OrderID) ON ProductsTTL.ProductID = [Order
Details].ProductID;
(All this inner join right and left I don't understand)
BUT... the question still is if the person adds qtyneeded to the box on the
qryfrm how does all of the data get saved in the orders and orders detail
tbls? And can I have the check box so that they can see that they ordered an
item on the list without it being bound to the ProductsTTL table?

option). or you could open a recordset in VBA to retrieve the fields, and
assign the values to one or more unbound textbox controls.

I don't know how to do this...

I am sorry that I can't make this more clear... I am so new and conceptually
I know what I want to do but I don't have the skill yet to accomplish it. I
know the order detail should be able to be viewed by the form I design even
if it is just numbers etc in the table and that I should be able to
manipulate it for reports/invoices etc. But, I am researching/reading looking
at others examples until my eyes blur and then I still don't know what step
to start with. And now half of my forms don't work because the qry is

wrong...

 

[lmv]

Ok...
(Table)ProductsTTL
ProductID(primary key,#)
ProductName (Text)
SupplierID (Text) Lookup to qrySupplierLookup)
SupplierCode (Text)
CategoryID (Foreign)#
UnitMultiple (Text) (SELECT tblUOM.UnitMultiple FROM tblUOM ORDER BY
tblUOM.UnitMultiple
UnitPrice (currency)
Quantity (#)
QTYNeed (#)
Order (ck box)Linked to QTYNeed
LastEdited

(TableOrders
OrderID(primary key,#)
ProjectID (Text)(SELECT Projects.ProjID, Projects.ProjectName FROM Projects
CategoryID (# Lookup to Categories table)
PurchaserID (Text)
RequestedBy (Text)(SELECT Overseer.[Last Name], Overseer.OverseersID FROM
Overseer ORDER BY Overseer.[Last Name]
SupplierName (Text)(qrySupplierLookup)
SupplierCode (Text)(SELECT ProductsTTL.ProductName, ProductsTTL.SupplierCode
FROM ProductsTTL
OrderDate (Date/time)
RequiredDate (Date/time)
Description (text)
ProductID (Text) SELECT (DISTINCTROW ProductsTTL.ProductID,
ProductsTTL.ProductName,
UOM (SELECT tblUOM.UnitMultiple FROM tblUOM ORDER BY tblUOM.UnitMultiple
Price (currency)
LastEdited (Date/time)

Table:Order Details
OrderID(primary key,#)
ProductID(primary key,#) (composite)
Quantity (#)
UnitPrice (Currency)

Categories.CategoryID 1:n ProductsTTL.CategoryID
ProductsTTL.ProductID 1:n Order Details.ProductID
Order Details.OrderID 1:n Order.OrderID
Orders.PurchaserID 1:n tblPurchaser.PurchaserID
Orders.ProjectID 1;n Projects.ProjID

My biggest problem is if I need to get the info from the ProductsTTL table
to append to my orders table... when a QTY needed is entered and checked
off...

thanks!
lmv

tina,
Thanks for the response...

it's not clear what you mean here. if you're *literally* storing product
data in the orders table, that is duplicating data.

You're right all I need to "store" is the qtyneeded & a Nunitprice (new)
All other info should be lookup...
So you are saying my Order Details Tbl just needs ProductID though my form
should be linked with a query to the productTTL the Order and Order Detail...
I don't want a combo because I want to see ALL that categories products
from the products tbl which will be sorted by category automatically when the
person selects what category (cbo to the categories tbl) They will see it in
the subfrm in my orderfrm.

So, I have a form based on a qry with alot of glitches at this point
following

SELECT ProductsTTL.*, Orders.OrderID, Orders.ProjectID, Orders.CategoryID,
Orders.Department, [Order Details].NUnitPrice, [Order Details].Quantity,
Orders.PurchaserID, Orders.OrderDate, Orders.RequiredDate, Orders.Description
FROM ProductsTTL LEFT JOIN (Orders RIGHT JOIN [Order Details] ON
Orders.OrderID = [Order Details].OrderID) ON ProductsTTL.ProductID = [Order
Details].ProductID;
(All this inner join right and left I don't understand)
BUT... the question still is if the person adds qtyneeded to the box on the
qryfrm how does all of the data get saved in the orders and orders detail
tbls? And can I have the check box so that they can see that they ordered an
item on the list without it being bound to the ProductsTTL table?

option). or you could open a recordset in VBA to retrieve the fields, and
assign the values to one or more unbound textbox controls.

I don't know how to do this...

I am sorry that I can't make this more clear... I am so new and conceptually
I know what I want to do but I don't have the skill yet to accomplish it. I
know the order detail should be able to be viewed by the form I design even
if it is just numbers etc in the table and that I should be able to
manipulate it for reports/invoices etc. But, I am researching/reading looking
at others examples until my eyes blur and then I still don't know what step
to start with. And now half of my forms don't work because the qry is

wrong...

 

[tina]

first of all, it appears that you're using Lookup fields in your tables.
recommend you get rid of them, see
http://home.att.net/~california.db/tips.html#aTip8 for more information.

let's look at the declared relationships:

Categories.CategoryID 1:n ProductsTTL.CategoryID
so one category may have many products, but each product belongs to only one
category. okay, makes sense.

OrderDetails.OrderID 1:n Orders.OrderID
so one order detail record may belong to many orders, but each order has
only one detail record. this relationship seems to be backward. usually an
Order record contains data that applies to an entire single order - such as
Customer, DateOrdered, etc. an OrderDetail record usually contains data
about each specific product included in one order, so "one order may have
many details records, but each detail record belongs to only one order".

Orders.PurchaserID 1:n tblPurchaser.PurchaserID
ditto this relationship - backwards. normally one purchaser may have many
orders, while each order belongs to only one purchaser.

and ditto again on Orders.ProjectID 1:n Projects.ProjID - probably should be
Projects.ProjID 1:n Orders.ProjectID

other than that, you have some normalization issues, such as:
1) table ProductsTTL probably should not have a SupplierCode field in it.
i'm guessing that SupplierCode describes a supplier; since the SupplierID
field already links a product record back to a specific supplier record, you
don't need another "suppliers field" in the products table.
2) since each product is linked to a supplier, you probably should not link
each order to a supplier, so the SupplierName and SupplierCode fields do not
belong in the Orders table.
3) i don't know what a UOM is, but since each product is linked to a
specific record in the UOM table, again you probably should not have a UOM
field in the Orders table.
4) you have an OrdersDetail table to list each product that is part of each
order, so you should not have a ProductID field in the Orders table.
5) and again, each product is linked to a category in table ProductsTTL, so
you probably should not have a category field in the Orders table.

i really urge you to read up on relational design principles before you go
any further. poorly structured tables will cause problem after problem for
you, as you begin to build queries, forms, and reports. recommend you go to
the link i provided elsewhere in this thread, and use those resources to
learn proper data normalization.

hth

Ok...
(Table)ProductsTTL
ProductID(primary key,#)
ProductName (Text)
SupplierID (Text) Lookup to qrySupplierLookup)
SupplierCode (Text)
CategoryID (Foreign)#
UnitMultiple (Text) (SELECT tblUOM.UnitMultiple FROM tblUOM ORDER BY
tblUOM.UnitMultiple
UnitPrice (currency)
Quantity (#)
QTYNeed (#)
Order (ck box)Linked to QTYNeed
LastEdited

(TableOrders
OrderID(primary key,#)
ProjectID (Text)(SELECT Projects.ProjID, Projects.ProjectName FROM Projects
CategoryID (# Lookup to Categories table)
PurchaserID (Text)
RequestedBy (Text)(SELECT Overseer.[Last Name], Overseer.OverseersID FROM
Overseer ORDER BY Overseer.[Last Name]
SupplierName (Text)(qrySupplierLookup)
SupplierCode (Text)(SELECT ProductsTTL.ProductName, ProductsTTL.SupplierCode
FROM ProductsTTL
OrderDate (Date/time)
RequiredDate (Date/time)
Description (text)
ProductID (Text) SELECT (DISTINCTROW ProductsTTL.ProductID,
ProductsTTL.ProductName,
UOM (SELECT tblUOM.UnitMultiple FROM tblUOM ORDER BY tblUOM.UnitMultiple
Price (currency)
LastEdited (Date/time)

Table:Order Details
OrderID(primary key,#)
ProductID(primary key,#) (composite)
Quantity (#)
UnitPrice (Currency)

Categories.CategoryID 1:n ProductsTTL.CategoryID
ProductsTTL.ProductID 1:n Order Details.ProductID
Order Details.OrderID 1:n Order.OrderID
Orders.PurchaserID 1:n tblPurchaser.PurchaserID
Orders.ProjectID 1;n Projects.ProjID

My biggest problem is if I need to get the info from the ProductsTTL table
to append to my orders table... when a QTY needed is entered and checked
off...

thanks!
lmv

tina,
Thanks for the response...
it's not clear what you mean here. if you're *literally* storing product
data in the orders table, that is duplicating data.
You're right all I need to "store" is the qtyneeded & a Nunitprice (new)
All other info should be lookup...
So you are saying my Order Details Tbl just needs ProductID though my form
should be linked with a query to the productTTL the Order and Order Detail...
I don't want a combo because I want to see ALL that categories products
from the products tbl which will be sorted by category automatically

when
the

person selects what category (cbo to the categories tbl) They will see

it
in

the subfrm in my orderfrm.

So, I have a form based on a qry with alot of glitches at this point
following

SELECT ProductsTTL.*, Orders.OrderID, Orders.ProjectID, Orders.CategoryID,
Orders.Department, [Order Details].NUnitPrice, [Order Details].Quantity,
Orders.PurchaserID, Orders.OrderDate, Orders.RequiredDate, Orders.Description
FROM ProductsTTL LEFT JOIN (Orders RIGHT JOIN [Order Details] ON
Orders.OrderID = [Order Details].OrderID) ON ProductsTTL.ProductID = [Order
Details].ProductID;
(All this inner join right and left I don't understand)
BUT... the question still is if the person adds qtyneeded to the box

on
the

qryfrm how does all of the data get saved in the orders and orders detail
tbls? And can I have the check box so that they can see that they

ordered
an

item on the list without it being bound to the ProductsTTL table?

option). or you could open a recordset in VBA to retrieve the fields

,
and

assign the values to one or more unbound textbox controls.
I don't know how to do this...

I am sorry that I can't make this more clear... I am so new and conceptually
I know what I want to do but I don't have the skill yet to accomplish

it.
I

know the order detail should be able to be viewed by the form I design even
if it is just numbers etc in the table and that I should be able to
manipulate it for reports/invoices etc. But, I am researching/reading looking
at others examples until my eyes blur and then I still don't know what step
to start with. And now half of my forms don't work because the qry is

wrong...