Using Look Up Wizard in a table

[Guest]

Hi, Group! Good day!

One of my officemates is tasked to do an inventory database using access
2000. He asked me to assist him though my knowledge in Access is very very
limited. He has tables namely Products, Supplier Transactions, Client Orders.
Table fields are as follows:

PRODUCTS - ProductCode (Pkey - text), ProductName (text)
SUPPLIER TRANSACTIONS - TransNumber (Autonumber - PKey),Date,SupplierCode,Qty
CLIENT ORDERS - PO#(PKey -
text),InvoiceNumber,OrderDate,ShipDate,QtyRequired,QtyShipped

What he did was make a Supplier Transactions Form which he wants to make the
data entry point form. He also made a Main Form named Products with unbound
textboxes namely TOTAL ORDERS OF CLIENTS, TOTAL DELIVERIES OF SUPPLIERS then
immediately below is a Tab Control containing 2 subforms namely CLIENT ORDERS
and SUPPLIER DELIVERIES. Now, he wants the data entered from the Supplier
Transactions Form to be automatically inserted into the subforms.

I can think of using a Look Up Wizard to build additional tables namely
PRODUCTS SUBFORM CLIENT ORDERS and PRODUCTS SUBFORM SUPPLIER DELIVERIES. But
I myself is confused how to do this. Any other suggestions?Please help us.
Thank you very much!

Sorry for the lengthy post but I think I have to recreate and give the full
details of the problem. Again, thank you so much for your time and effort
helping us.

 

[Steve Schapel]

RAM,

I believe the design of the tables should be the first step, and any
thoughts about Forms should be eliminated until the table design is
sorted. After that, the structure of the forms need to serve the
structure of the tables, not the other way around. In this case, it
would seem that some more work needs to go into the table design. There
appears to be no connection between these three tables. There appears
to be no way to associate a Product with a particular Supplier. In the
ClientOrders table, there appear to be Quantity fields, but no
indication quantity "of what". I haven't quite grasped the meaning of
the SupplierTransactions table, but there also seems to be some key
information missing here as well. Could you please give some further
attention to this?

By the way, as an aside, a couple of comments on the field names. With
PO#, it is not a good idea to use a # as part of the name of a field.
And 'Date' is a Reserved Word (i.e. has a special meaning) in Access,
and as such should not be used as the name of a field or control.

 

[Guest]

Hi, Steve! Thanks for the prompt reply and for the pointers. I appreciate
that really.

Anyway, regarding table design, I guess it is the relationship? Am I correct?
Here are the tables again:

PRODUCTS - ProductCode (Pkey - text), ProductName (text)
SUPPLIER TRANSACTIONS - TransNumber (Autonumber -
PKey),TransDate,ProductCode,Qty_Delivered,SupplierCode
CLIENT ORDERS - PONumber(PKey
-text),InvoiceNumber,OrderDate,ShipDate,ProductCode,QtyRequired,QtyShipped


SUPPLIER TRANSACTION TABLE - this will serve as the data entry point.
All entries at this table will also automatically be entered into PRODUCTS
Table (through PRODUCTCODE field) and CLIENT ORDERS Table (also through the
aid of PRODUCTCODE field).

I can't tell you exactly what we want the table to be but when you try to
view the Supplier Transactions Table in Datasheet it would look something
like this:

+Supplier Transactions Table
+Products Table
+Clients Order Table

May we as well ask for your opinion as to how we would relate these tables.

Sir, please bear with me. Trying hard to tell you exactly what's the design
of our table but my vocabulary's limited. Just can't find the right words

Thank you very much for your time and effort....and for the patience

 

[Steve Schapel]

RAM,

Yes, the tables do need to have a logical relationship to each other.
But related tables don't work in the way you were suggesting... entering
data into one table will not cause data to automatically be entered into
another table.

It looks like the Products table is a sort of "master list" of all
products. This will require a separate data entry process, via a form
specifically for the purpose of entering/editing the products.

Then, the Supplier Transaction table describes the data related to you
receiving shipments of a product from a Supplier, right? So, fair
enough, it will record the date you received delivery, which product you
received, which will be looked up to the Products table, the quantity,
and I assume that SupplierCode is to identify the supplier. But again,
this is a separate process, and you will need a form for managing this.

And then, Client Orders. Well, once again, this is a completely
separate process, and (unless I am misunderstanding your purpose) it can
not be related to the Supplier Transactions at all. So you will need
another form for this. What I notice here is that there is no
information about the Client, i.e. who has ordered/received this
product. And also, I think it would be normal in this type of database
to have an Orders table, where you record the information that only
occurs one time for each order, such as Client and OrderDate, and
another table for OrderDetails where you record information where there
may be more than one item, for example the same client may order more
than one product on the same order.

There is a sample database called Northwind which comes as part of the
Access program. It is the same sort of database as what you are trying
to work with here. I would really suggest you study this database as it
will help you to see how things work.