need some help here with using a form to lookup data

[mikeaa101]

I am creating a database with a student of mine it has been a very long time since I used access to any great degree and I am very very rusty.

The database we are creating has 5 tables in it

Customers
Orders
Products
Billing
Vat

I am trying to create a form that will automatically create an order in the Orders table. The autonumber will generate a unique order number which is fine for this purpose however, I need the form to pull data from the products and customers tables. For example if I enter a product code into the form the database can use this information to fill other fields in the form such as price and product description (much like a VLookup in excel).

Can someone advise me on a simple solution or point me in the direction of a tutorial that may help

Thanks

Mike

 

[jzwp22]

What type of information is in the billing and Vat tables?

Further, if a customer can place an order for many products (not just one) that describes a one-to-many relationship between orders and products. Further, I assume that a product may be on many orders, so another one-to-many relationship. When you have two one-to-many relationships between the same two entities (orders & products in this case), you have a many-to-many relationship for which you need a junction table.

tblOrderDetails
-pkOrderDetailID primary key, autonumber
-fkOrderID foreign key relating back to the orders table
-fkProductID foreign key relating back to the product table
-longQty (the quantity being order)
-currUnitCost (the unit cost of the product being ordered)

Defining your table structure is critical to a successful application, so that should be done before you work on forms. But just to give you a little info on forms, you would base a main form on the order table and use a combo box based on the customer table to populate/tie the customer to the order. You would then use a subform on the order form. The subform would be based on the junction table I showed above. In the subform, you would use a combo box based on the product table to pull in that info.

Could you provide the fields of the tables you mentioned similar to what I showed above?

 

[mikeaa101]

Thanks for the reply

This is very much a work in progress so there are going to be many mistakes along the way

It will be a very small database and for a student assignment which is why I wish to keep everything as simple as possible. We are still in the design and test phase at present and have yet to normalise at all.


Two tables eg

orders currently has 6 fields

ordernumber, customernumber, ProductID, ProductDescription, Delivery Address

Components has 6 fields

StockReference, Component, Supplier, Price, StockLevel, MinimumStock

Customers has 10 fields

CustomerId, Title, FName, SName, AddressLine1, AddressLine2, Town, PostCode, TelNumber, Email

Billing has 9 fields

CustomerNumber, FName, SName, CreditCardNumber, Expiry Data, SecurityNumber, AddressLine1, AddressLine2, PostCode

Vat has 4 fields

ItemNumber, Component, PriceExVat, VAT

 

[jzwp22]

You mentioned a products table in your initial post but you provide field names for a components table. Are products and components the same thing?

The most important question at this point is whether an order can consist of more than one product/component.

BTW, normalization should be occurring in the design phase.

 

[finneyz]

for a web database this video is very helpful
http://www.youtube.com/watch?v=3wdjYIby_b0&list=PL27E956A1537FE1C5&index=2&feature=plpp_video

 

[mikeaa101]

Yes Products is components

In reality of course an order can consist of more than one component in practice for what we are doing if it makes it easier then would say it is not vitally important.

Thanks

 

[jzwp22]

Following the rules of normalization, if an order can consist of more than one component then you should set up your tables to handle it now. This is what the basic structure would look like:

tblOrders
-pkOrderID primary key, autonumber
-fkCustomerID foreign key to tblCustomers
-dteOrder (order date)

tblCustomers
-pkCustomerID primary key,autonumber
-txtCustName
-txtAddr
etc.

tblComponents
-pkComponentID primary key,autonumber
-txtComponentNo
-txtComponentDesc
-currUnitPrice

tblOrderDetails
-pkOrderDetailID primary key, autonumber
-fkOrderID foreign key to tblOrders
-fkComponentID foreign key to tblComponents
-longQtyOrdered
-currUnitPrice (the value from the components table is copied here to capture the unit cost at the time of the sale. That way you can adjust prices of components over time)

 

[mikeaa101]

Ah ok this is becoming a bit clearer now

I have just been looking online and found info about using a combo box in a form to call information would this work for me here do you think

http://youtu.be/vuiKb8jeKTM

Mike

 

[jzwp22]

I would typically use a main form based on the order table with a subform based on the tblOrderDetails. In that subform, you would use a combo box for the components (the combo box would be based on the components table)

 

[mikeaa101]

Great thanks alot mate you have been a big help

Mike

 

[jzwp22]

You're welcome.