Help with Order form design (1 Viewer)

age0004 · Apr 3, 2019

Hello,

I am having trouble with designing an order form for this database because of the way that the tables are designed.

Situation: There are tables "Parts" and "Part details" and "Vendors". I have created 2 one-to-many relationships here, with each Part description having multiple vendors. I have a composite key in part details so that there are no two part descriptions with the same vendor. "Part details" have the part numbers, vendors, prices, etc.

For order tables, I have "orders" and "order details".
Orders:
Order No. (primary key)
Company (related to customer table)
Order date
Employee (person who ordered)
Subtotal (calculated)
Taxes
Shipping
Total (calculated)

Order details:
Order No (from orders)
Order Detail (autonumber)
Qty.
Part description (??)
Part number (??)
Vendor (??) (??) as in im not sure where to pull these from (tbl or qry?)
Price (??)
Ext price

GOAL: I am trying to create an order form where the main form is linked to the "order query", with a subform linked to a "order detail query" so that I can make an order. The user has to be able to start an order on the main form, enter in the customer information, then start putting in what needs to be ordered. They should select the part description that they want from a combo box, which should make the part number combo box update to show all of the order options for that specific part description. Once the part number has been selected it will fill in the price and vendor associated with the part number.

PROBLEM: I cant figure it out.

Sorry for the length and thanks in advance.

Ranman256 · Apr 3, 2019

you need a tParts table. Its a lookup table the user can pick via listbox,
or combo box in the OrderDtl subform.
picking the part will add the PartDescr, Price, Vendor too.

if combo box method, it would have 4 columns,
add all items to the fields
me.partDescr = cboPart.column(2)
me.partPrice = cboPart.column(3)
etc...

age0004 · Apr 3, 2019

Okay, so once I do that with the look up fields and have them pick from a combo box, would I set the column values to the text boxes through VBA code on the after update event or on click event? Or just make each text boxes control source = column (#) from the combo box.

Does that make sense? Thanks

plog · Apr 3, 2019

Throw away your form for now, focus on getting your tables correctly set up. Then, you should move to making Reports to verify you can get the data out like you want. Finally, you work on forms--no point making beautiful forms if you are just throwing their data into a useless pit of tables.

In your database, go to the Relationship tool, bring in all your tables, set up the relationships, expand your tables so we can see their names and all fields and then post back here a screenshot of it. With that we can work through setting the tables up properly.

age0004 · Apr 3, 2019

Sounds great. So this is what I already had set up. The only link that I am missing is where to get the part information from in the "order details". (well unless you have a better way)

Let me know if the link doesnt work.
THX

age0004 · Apr 3, 2019

https://www.dropbox.com/s/comwjtrlkcve34y/Capture.JPG?dl=0

jdraw · Apr 3, 2019

Further to plog's advice, it is easier for reader and you to understand your requirement when you describe "the business" in simple, plain English --not in terms of tables and forms.

Access does not work well with field and/or control names with embedded spaces or special characters(. - # etc). Best to stay with alphabetic and underscore(_) to reduce syntax issues.

Good luck with your project.

age0004 · Apr 3, 2019

In response to jdraw:
No problem! Basically we are a security company. We have hundreds of clients and even more projects. The goal right now is to have all of our customers, products (that we have to specially order most of the time), vendors, users, and employees.. etc.

We order these parts from various vendors depending on which is the cheapest or most convenient etc. We have to document all of this stuff so its easier to have it all done in one system to so that we can track orders rather than the way that they are doing thing now (which is a massive headache)

plog · Apr 3, 2019

First the simple rules about tables:

No spaces in names. It just makes coding and querying harder down the line. Simply remove them (Customer Job Locations -> CustomerJobLocations)

No calculated fields in tables. Instead build queries to determine that data if needed.

For the parts issue you need to decide how granularly you want to track them. Let's use a piggy bank database as an example--I can track my money specifically:

On 2/2/2019 I put $5 bill with serial number #123456 into the bank
On 3/3/2019 I used $5 bill with serial number #123456 to buy a hamburger

Or I can track generally:

On 2/2/2019 I put $5 into the bank
On 3/3/2019 I used $5 to buy a hamburger

That $5 I used on 3/3 may be the same one as I put in on 2/2 or it might not be--I don't care in this situation because tracking my bills specifically is overkill. There's no need to be able to trace that specific bill all the way through its origin and use; $5 is $5. But I don't know if that's the case in your database. So, how granularly do you need to track parts?

As your database is set up now, you are doing neither--your are tracking parts by vendor. You don't know when a specific part came into inventory, but you do know from who you got it. Is that really important? If you have 5 of the same parts all from different vendors do you seperate them in the warehouse by vendor? When you need one of them are you careful to select the correct one even though they are the exact same part?

Explain more about your parts and why you can't treat them like generic $5 bills and throw them all in the same piggy bank and pull out what you need when you need it. Also, explain what a Job Type is and why a part needs it assigned to it.

age0004 · Apr 3, 2019

Okay so,

I see what you mean because the way the we run "inventory" is not a typical way you would think of inventory. Once we get an work order in, we order the equipment for the job and as soon as it comes in it leaves the building so the techs can do the job. So in this case we only care which vendor we ordered it from, what the part number and price was with that specific vendor, and who we ordered the part from. The parts that we order actually do have different part numbers depending on the vendor so that just makes things more difficult.

Ill go ahead and remove spaces from table names to make things easier.

As far as "tracking" goes, just being able to go back and see and search for the orders and details is good enough. All we would need to do is go back in later and enter in the date that we rec'd the parts. This is the only "tracking" that we do since we are not concerned with prices as much as knowing whether our orders have come in yet or not.

For job types, each part is used for a certain thing... for instance we set up cameras, security, gates, access, lighting fixtures, other misc things. (the request to have these categories assigned to each part so they are easier to find in the database) Thats all

Thanks for being so patient with me, you're the nicest person I've worked with on here so far. Appreciate it!

plog · Apr 3, 2019

Once we get an work order in, we order the equipment for the job and as soon as it comes in it leaves the building so the techs can do the job.

Then why do you have a [Location In Warehouse] field?

The 4 tables at the bottom just don't make sense. I really think the data they hold should most likely go into OrderDetails. Actually it looks like OrderDetails already has fields to accomodate the data in Part Details.

age0004 · Apr 3, 2019

Ha! Yeah thats not supposed to be there. I had that there originally but need to delete it. I wont have either quantity or location within this table anymore. Quantity was moved to part details (because some things come as sets, so its actually quantity per order, not how many we have here) .. . Ive fixed this now.

As far as an "Orderdetails" table goes, yes, the part description, part number, vendor, and price will all be included as in the picture... but this information has to come from somewhere and I have to be given those option when I go to order on an order form. So If I just use the "PartDetails" table how can I set this up so that they 1st choose the part description, then choose part number, then fills in the rest of the information ...vendor, price, extended price (Qty*price), .....blah blah
Thanks

plog · Apr 3, 2019

how can I set this up so that they 1st choose the part description, then choose part number, then fills in the rest of the information

Data doesn't get moved around in a database, it gets referenced. First, read up on the concept called foreign keys (https://en.wikipedia.org/wiki/Foreign_key). You are already doing that, but incorrectly.

OrderDetails contains a field called Vendor and you use it to link to the Vendors table via the text in the Vendor field. You got the concept right, but the execution wrong.
Instead it would be better to put an autonumber primary key in Vendors (e.g. VendorID) and then store that number in OrderDetails to establish the Vendor relationship.

Then, to solve the Part/Vendor/Price issue you need a new reference table that uses that concept:

tblVendorParts
vp_ID, autonumber, primary key
vp_Vendor, number, foreign key to Vendors.VendorID
vp_PartNumber, text?, this holds whatever is in PartNumber now
vp_PartDescription, text, this holds whatever is in PartDescription now
vp_Price, number, price of this part from this vendor

That will be the table you use for your drop downs. It will hold every part/vendor permutation available. Then, in OrderDetails you remove the Vendor, Price, Part Number and Part Description fields and replace it with the vp_ID field.

With that one number in a OrderDetails record you now have access to all the other data in tblVendorParts. So now you don't directly store it with an orderdetail, you reference it.

age0004 · Apr 3, 2019

Okay, so I updated some things and changed what you've said. The only part i'm not understanding is the partdescription, partnumber, and price fields being set in on the table. I have what type of data filled in but should I use the lookup wizard here, set up the relationships? Sorry that part I was not quite grasping. Here is my updated relationship table.

https://www.dropbox.com/s/7lzpv6dnh57fr9e/Capture1.JPG?dl=0

I also am not sure how to relate the list of parts with their part numbers ("PartsTable" and "PartDetails")

plog · Apr 3, 2019

Theres no need for PartDetails, the goal is to store the data in the least amount of fields/tables. All the data in there is now in VendorParts. PartDetails should go.

I don't understand JobType field still. Are you sure that's an attribute of a part and not an Order? If it really does go with a Part then it needs to go into the VendorParts table

age0004 · Apr 3, 2019

There is a need for it, just what has been requested. So ill go ahead and put that with the vendorparts table and get rid of this side and go from there.

With setting up an order form, you mentioned setting up a report first. This is not something im firmilar with in comparison to forms. So, when setting it up will I link the report to the orderdetails table?

Thanks

plog · Apr 3, 2019

Reports really depend on what you want to see. Most likely they will be based on a query.

My suggestion is to populate your tables with some fake data, determine what data you want out via your Reports and try and make those reports. That would be a good way to test the table structure.

age0004 · Apr 3, 2019

Okay Im going to set up a report as best as I can, from there if I send over a screenshot or maybe a separate demo file could you possibly critique it?

You've been great, really. thanks

plog · Apr 3, 2019

Yes, just upload it here.

age0004 · Apr 7, 2019

I have the order form and the report for the form working almost perfectly now. The only issue I am having now is I am trying to figure out how to get the "extended price" calculation to work. I need to multiple the Qty (which is coming from the orderdetails table) * the vpPrice (coming from vendorparts table.
I have tried a couple of queries and trying to set the control source to do that however its not storing that information in the table so im not quite sure how to go about fixing this. (As in doing a formula as the control source works on the form but doesnt store in the table of course) I am also having that issue with the subtotal (SUM of Ext price) and Total (SUM of Subtotal+taxes+shipping)

Any ideas?

Thanks for all of your help before.

Help with Order form design (1 Viewer)

Registered User.

Well-known member

Registered User.

Banishment Pending

Registered User.

Registered User.

Super Moderator

Registered User.

Banishment Pending

Registered User.

Banishment Pending

Registered User.

Banishment Pending

Registered User.

Banishment Pending

Registered User.

Banishment Pending

Registered User.

Banishment Pending

Registered User.

Similar threads

Users who are viewing this thread