Data Normalization and applying Auto Number fields

[taholmes160]

Good Afternoon everyone:



I have a database project that I am working on. In its current form it is working correctly, however before I go any further, I want to check and make sure that I have gotten the data laid out in the proper form, and have the proper field types in place, I have had people go over it for me, and I THINK / HOPE that I have managed to catch and correct all the errors that they pointed out, but I would really appreciate it if someone (several someones) could go over it for me and make sure, before I attempt to develop it any more.

The other problem that I am having, is that when I set up some of the data, I didnt/couldnt get the primary key information right, specifically, they needed to be auto numbers specifically:

TblBusinesses - CompanyID_PK
TblCommodities - CommodityID_PK
TblRoads - RoadsID_PK

The problem being that with these not being Auto Number fields, I cannot (as far as I can tell) develop forms to add entries to them. Yes, I can do it in the datasheet view, but thats really clunky. I also know that I can just start over with the bulk data, and if thats the best solution, I will, but if theres a better way, I'd be interested in hearing it

Thanks for your input
TIM

Original Data Original_Data.zip
Current Database SLRG_Waybills_2_1 to send.zip

----EDIT Please note that a post similar to this one was posted on UtterAccess forums in order to cast a wider net and receive additional input /EDIT ----

[June7]

You should also provide link to other forum post https://www.utteraccess.com/forum/in...pic=2051907&hl=

Yes, forms can be designed for data entry to records even if you don't use autonumber as PK. Either type the value you want or write code to build the identifier. Generating custom unique identifier is a common topic.

[taholmes160]

Im Sorry to all, I goofed and neglected the link to the other forum -- I'll try to do better in the future

TIM

[June7]

Don't see any form/subform arrangements.

I do see 3 forms for entering Waybill data. The only reason is for different cascading combobox setups. I expect these could be combined to one form.

[taholmes160]

Hi June:

There are no form / subform arrangements because I (a) dont know how to make them, and (b) didnt realize that they would be somehow useful in this situation.

As far as 3 waybill creation forms, they are different because I didnt know how else to do them and achieve the desired effect, which is to be able to easily find the data needed to construct the waybills

I have a lot to learn in access, thats for sure -- I appreciate all your help

TIM

[orange]

Tim,
I have responded to your thread https://www.accessforums.net/showthr...620#post416620

This is what happens when you multi-post and cross post.

Hint: If it's the same subject, stay in the thread. I you have to start a new thread, provide some coordinates and tell us so along with a link, so we aren't answering something you have forgotten or have quit working on.

[taholmes160]

Yep, I see the problem, I started a new thread b/c someone in my original thread said to keep starting new ones b/c old ones dont get read and answered as much -- LOL -- oh well -- live and learn

[orange]

Tim,

I have no real issue with you starting a new thread, if:
-it's a new topic
-you have tried suggestions and nothing is working
-you want to refresh/rethink and approach
BUT
I suggest you say/inform readers - I'm changing topics or similar and leave a link to the next thread, and make a reference back to the original/previous.

Several people on this and other forums monitor several forums/threads and don't necessarily recall that independent posts are related or are part of same topic.
As you have undoubtedly found by now, many posters "falsely assume" readers understand every hint or subtlety written by the poster. Readers only know what posters tell us in their post(s). These are often without context; have no supporting code; rarely are described clearly; often include "jargonese" that is specific to their business; or have "quasi-code/quasi-SQL" that they have concocted to show their efforts/intents.
Anyway, bottom line is, as always, clear communications and context simplify understanding and lead to focused responses.

Good luck with your project.

[taholmes160]

Thanks a whole bunch -- I really appreciate it and all the help --

Have a great evening

TIM

[June7]

One example of a form/subform arrangement would be: Main form bound to an Orders table and a subform bound to OrderDetails with combobox to select product.

TblWaybills is a 'junction' table. Its records establish association between commodity, shipper, consignee, car code.

You are correct that form/subform arrangement in your db may not be needed. Having a single form bound to TblWaybills and 4 comboboxes to select values is valid approach but I do think you don't need 3 variations of this.

[taholmes160]

Cool, that makes sense, I'll have a look at that as far as the TblWaybills, Im not quite sure how that would work out though, because the order things are selected in is important (i think)

Thanks a lot for your input
TIM

[taholmes160]

Good Morning to all:

In a previous message, @June7, said

Having a single form bound to TblWaybills and 4 comboboxes to select values is valid approach but I do think you don't need 3 variations of this.

Since I got that message, ive been trying to think of how to accomplish that, because the idea intrigues me. Unfortunately, I have been unable to come up with an idea of how to accomplish it. I would love to hear/see everyones suggestions on how to accomplish this

Thanks again for all the help

TIM

[June7]

The only real difference I see between the forms is the order of controls for data entry. All the same data is still required for a complete record. UNBOUND State and City comboboxes are used to conditionally filter the Consignee and Shipper comboboxes. This can all be done on one form. Consider:


The WaybillID control is set Enabled No.

[taholmes160]

June7:

WOW -- that looks awesome -- my question is this, if I have a shipper, and need to figure out what commodities he ships, and who receives that commodity, how do I make this form do that? -- cuz it looks WOW

Thanks
TIM

[June7]

Exactly what is the work flow? A waybill can be initiated by either shipper or consignee? So shipper or consignee says they need to ship (or receive) a particular commodity then you have to locate the other based on commodity? Is there a situation where you have a commodity then have to locate shipper and consignee?