Goods Order Form

[amraam840]

No introduction forum so... Hi, I'm new to Access. Maybe some pros here can point me in the right direction because I feel like this is about this far >< beyond my grasp of this software. I am familiar with forums sites so I have searched but I'm afraid the more specific I make my searches (here or Google), the more vague and off topic the results are. I've completed countless tutorials but I must have missed the one that hits the nail on the head here...



I've got a small nonprofit selling shirts with 4 designs and 6 sizes. I have made a form with a subform that links customers to their orders but I can only order one size per design or vice versa.

What am I looking at here to get one form per customer with subform for orders with multiple items per order, with text boxes for quantities, combo boxes for sizes and designs, and to calculate a running total price per order?

I have separate tables for customers, designs, sizes (which price is based on), and orders.

I have many more questions for other aspects of this database as well as others and I hope to gain a great deal of knowledge from this community that I may help others someday. This is the only part of my project that fits in this subforum. Okay, let me hear it; criticism, comments, and outright solutions are welcome.

[hertfordkc]

No problem with the Customer table, as long as it contains only info basic to that customer.
In thinking about your problem, I would call the Designs table AvailableDesigns.
Likewise, AvailableSizes which would include price info.
Next AvailableShirts which enumerates the design-size availability.
OrderedShirts would contain the index from AvailableShirts and quantity ordered.
Orders would contain OrderNo, CustomerID, and OrderShirt Index.
Defining the relationships will be critical to making it work, but should make it easier to design the user interface.
I hope what I've outlined is helpful. I'm sure there are other ways of solving the problem.

[amraam840]

Okay, great help! But now I'm finding that the $Total is calculating based of the [quantity]*(primary key)of[size] instead of [quantity]*[cost]. I suppose I'll have to add another field into the orders table showing the cost of each size???

[hertfordkc]

Okay, great help! But now I'm finding that the $Total is calculating based of the [quantity]*(primary key)of[size] instead of [quantity]*[cost]. I suppose I'll have to add another field into the orders table showing the cost of each size???

IMHO, tables should contain very basic and unique pieces of information, and anything that can be calculated should be handled by queries and reports. By breaking the tables down as I conceived them, you really need to use queries to bring together user friendly names, size, prices and quantities. You may need a calculated field on some queries to extend quantity * price. You can have another query do order total, i.e sums quantity * price. if you need to show that on a form. (You may have to use event properties to requery in order to keep the total up to date.) Also, you will find the reports will group orders, sizes, etc for you and run totals.

[amraam840]

Hm, queries... Unexplored territory.

[hertfordkc]

Use the design view and explore. Unless you already know SQL, I wouldn't start there.
Most reports and forms are based on queries rather than directly on tables.

[amraam840]

Well SQL is something I think I'll have to look into out of neccessity. Someday I want to have my webpage hosted on my own dedicated pc and I've tried that a little bit and it involved SQL but I was way over my head. For now, I'll just stick with the basics but I need to figure out what basics I need..... As in getting relationships just right and figuring out what all needs it's own tables...

[hertfordkc]

that if you have no experience with it, I think switching between design view and SQL view in query design is much easier than trying to experiment by writing SQL statements.
Good luck.