Re: Need help setting up a Parent form with three subforms

Hi Bernard,

you're welcome

~~~
Parts

yes, PartID -- autonumber = PK

do not use DESCRIPTION as a fieldname, it is a reserved word

Problem names and reserved words in Access, by Allen Browne
http://www.allenbrowne.com/AppIssueBadWord.html

PartNumandDesc -- no ned to store this in Access, it can be easily combined anytime to display or let the users pick from in a combo or listbox

Stock_Um -- what is this?
Commodity_Code -- what is this?

MFG_Name

you should make a Manufacturers table with MfgID autonumber and MfgName. Move the text to that table, get tthe ID, and store the ID in Parts as a long integer

Drawing_File ... just one?

Pref_SupplierID
I would rename this --> SupplierID_pref
not a big deal really, but I like to have the beginning of the name indicate what the field actually is and then qualify it if necessary

Primary_Loc_ID
would rename --> LocID_Primary

Unit_Mat_Cost
wow! you are lucky if your costs are always the same! My experience is that they are not ...
would rename --> Cost_Mat
(it is for unit unless specified otherwise)

Unit_Price --> Price
(it is for unit unless specified otherwise)

UseThisPrice
what is the difference from Unit_Mat_Cost?
I generally think of Cost as what you pay and Price what you charge to customers ... just to adopt a convention in naming fields

"What field needs
to be designated the Foreign Key or do I need to add a field to create a FK?
Are there other fields I need to add to this specific table?"

MfgID, LocID, SupplierID, and maybe CommodityID (depending on what Commodity_Code is)

~~~~~~~~~~~~~~
Customers

do not use TYPE as a fieldname, it is a reserved word

personally, I store addresses, phone numbers, email addresses, and websites separately as a person/company may have one or more of each. I see you have CustPhone, CustFax, CustMobile -- these would all be records in a related Phones table. What if a company has more than one landline? More than one mobile number? More than one fax? what if they have something else, like a pager?

CustID would be the PK, yes -- but you need to make sure the same customer is not in there multiple times -- this often happens when you bring data in from other places.

"Do I add
additional columns to include shipping and installation address fields in
this specific table? "

this is one reason I store addresses in a separate place. My philosophy is that each address should be in the database just one time. Then, you would have a table to relate customers and addresses with an address type (shipping, billing) that stores CustID, AddressID, and an AddressTypeID.

But customers are not the only entities that need contact informaiton -- so do suppliers, vendors, manufacturers, contacts, etc ...

What I do is create a People table and put everybody there -- just like the phonebook. the autonumber is PeopleID (or PID).

if that person/company is a customer, a new record is created in customers and instead of CustomerID being an autonumber, it takes its value from PID.

same thing with Suppliers -- SupplierID is a long integer in the Suppliers table and the record must first exist in customers.

I know this adds complexity, but this is one of the reasons that I chose to use contact information for my tutorials -- wish I had more of them done!

"THE QUOTE FORM"

but you must think in terms of tables ...

What you are calling 'Quote Details' I would call Quotes -- whatever is your header information.

QuoteID, autonumber, is the PK

ContractID indicates that perhaps there is a Contracts table with contract informaiton and this field references it? If so, it is a FK

you should store CustomerID -- you can get the name anytime

EquipmentModel, EquipmentSerialNumber -- would you not have a table to keep track of Equipment and simply store the EquipmentID as a FK?

Billing, Shipping, Installation fields -- probably no way around storing those. Even if you have addresses in your system, they may change and a quote should specify exactly where things went and who neds to pay. I am not really sure of the difference between Shipping and Installation... does your company go do installations?

starting with quantity, you would create a QuoteDetails table:

QuoteDetails
- QuotDetID, autonumber, PK
- QuoteID, long integer -- FK to Quotes
- PartID, long integer, FK to Parts
- Qty, number
- Cost -- if your costs are different, as most places usually are, then you cannot store cost here as they may have 50 of something and the cost for 15 was one thing, the cost for 25 something else, and the cost for the rest different as well
- UnitPrice, currency
- Discount -- do you apply this on an item-by-item level or the whole Quote?
CustPrice -- this should not be stored, it can be calculated anytime --> Qty * UnitPrice

and then, for the other fields -- what I already said ;)

What I suggest you do is FORGET you are getting data from Excel. Set up the tables the way they need to be. Draw your relationships. Enforce Referencial Integrity. Print the relationship diagram and take it with you everywhere. Look at it whenever you can.

Design is an iteractive process and should take a lot more time than most people give it.

Read the Normalization and Relationships sections of Access Basics:

Access Basics by Crystal
http://www.accessmvp.com/strive4peace
free 100 page tutorial that covers essentials in Access

do not think about forms or reports right now -- think only of tables, fields, and relationships.


Warm Regards,
Crystal
remote programming and training

http://www.YouTube.com/user/LearnAccessByCrystal


*
(: have an awesome day :)
*


swatb98 wrote:
Crystal,

I have viewed and reviewed your youtube videos. They serve as a good introduction to the essence of access; however, I am having trouble applying them (due to my own shortcomings) to the specific details of my case.

So, I will attempt to add more detail to my thought process for the purpose of clarity.

1) PARTS TABLE: This excel table exists and was imported into access. It contains the following columns, which are already populated (there currently are 4,500 parts/rows):

PartID (Autonumber)
PartNum [the actual part number]
Description [the description of the part]
PartNumandDesc [this column concatenates the part number and the description into one cell]
Stock_Um
Commodity_Code
MFG_Name
MFG_Part_ID
Drawing_File
Pref_Supplier_ID
Primary_Loc_ID
Unit_Mat_Cost
Unit_Price
UseThisPrice [this column contains the actual cost of the part for my company]
Specifications

Am I assuming correctly that the PartID is the correct PK? What field needs to be designated the Foreign Key or do I need to add a field to create a FK? Are there other fields I need to add to this specific table?

2) CUSTOMER TABLE: This excel table exists and was imported into access. It contains the following columns, which are already populated (there currently are 300 customer/rows):

CustID (Autonumber)
CustName
Type
CustAdd1
CustAdd2
CustCity
CustState
CustZip
CustCountry
CustPhone
CustFax
CustMobile
CustEmail
CustWeb

Am I assuming correctly that the CustID is the correct PK? What field needs to be designated the Foreign Key or do I need to add a field to create a FK? Are there other fields I need to add to this specific table? For now, the customer address fields double as the billing address fields. Do I add additional columns to include shipping and installation address fields in this specific table? Do I add additional columns to include billing, shipping, and installation contact information in this specific table?


3) THE QUOTE FORM: At this point, I have trouble thinking in terms of tables. I can imagine having a customer call, filling all the information necessary in the form, which then stores that information in a table. Which table exactly, I don’t know. All I can do right now is describe to you which fields I would like to see on the subform. I need help with how they tables, form, and subforms all interact and inter-relate.

I am imagining an 8.5 x 11 *** of paper as the form. At the top right of the page, there would be fields containing the Quote Details, with the following fields:

QuoteID
QuoteDate
PromiseDate
ContractID
Purchase Order (drop down box with 3-4 choices)
Terms (drop down box with 3-4 choices)
EnteredByFullName

Which field needs to be the PK and which field, if any, needs to be the FK?

Then, underneath the top right corner is the following info contained on one line:

CustomerName (I imagine this to be a drop-down box which pulls the names from the Customer table)
EquipmentModel (For now, this field is manually entered by the user.)
EquipmentSerialNumber (For now, this field is manually entered by the user.)

Underneath this line is a three-tabbed field containing the customer addresses:

ShippingMethod
BillingName ShippingName InstallationName
BillingAdd01 ShippingAdd01 InstallationAdd01 BillingAdd02 ShippingAdd02 InstallationAdd02 BillingCity ShippingCity InstallationCity
BillingStateRegion ShippingStateRegion InstallationStateRegion
BillingZipPostal ShippingZipPostal InstallationZipPostal
BillingCountry ShippingCountry InstallationCountry
BillingContactName ShippingContactName InstallationContactName
BillingContactPhone ShippingContactPhone InstallationContactPhone
BillingContactExt ShippingContactExt InstallationContactExtension
BillingContactFax ShippingContactFax InstallationContactFax
BillingContactEmail ShippingContactEmail InstallationContactEmail

Underneath the tabbed field comes the ordering/quote info rows (one row per part):

Quantity
PartNumDesc (drop down with Parts Table as the source)
Cost (self populating based on PartNumDesc and Parts Table)
Markup
UnitPrice
Discount
CustPrice


Then, at the bottom of the page comes the final section of the form you described:
~~
TotalPrice -- this would go in the Quotes table, if you store it at all since it is a calculated field
Shipping -- Quotes table
TaxRate -- Quotes table
TotalTaxes -- this would go in the Quotes table, if you store it at all since it is a calculated field
FinalPrice -- this would go in the Quotes table, if you store it at all since it is a calculated field
Additional Notes -- Quotes or QuoteDetails, depending on where you need it


As you can tell, I have trouble visualizing this information in any other way than how I described. In order to accomplish this form, I need your guidance with respect to exactly what tables I need for this scenario (please forget that I mentioned the inventory control for now), which fields need to be the PK, which fields need to be the FK, and how to fit all of this info into one form which pulls information from the two tables and uses subforms for the purpose of data entry clarity.

Does any of this make sense? I am sorry this is taking so long to click in my head.
I really do appreciate all of your help on this matter.

Thanks so much,
Bernard

.

Quantcast