Need help setting up a Parent form with three subforms

[swatb98]

I apologize, I am new to Access. I need help executing the following concept.
When a customer calls to order parts, I would like to be able to create a
quote for the desired parts. I understand that this sets up as ONE customer
with MANY quotes and ONE quote with MANY parts.

So, I have setup a parent form (uCustomerBilling) with the following fields:

CustomerID Autonumber User Selects from Drop Down based on on
CustomerListMaster031909 Table
Equipment Model Text User Entered
Equipment Serial Number Text User Entered
Billing Name Text User Selected Drop Down or can enter new
Billing Address 01 Text Populates based on Billing Name or can enter new
Billing Address 02 Text Populates based on Billing Name or can enter new
Billing City Text Populates based on Billing Name or can
enter new
Billing StateRegion Text Populates based on Billing Name or can enter new
BillingZipPostal Text Populates based on Billing Name or can enter new
BillingCountry Text Populates based on Billing Name or can enter new
BillingContactFullNameText Populates based on Billing Name or can enter new
BillingContactPhone Text Populates based on Billing Name or can enter new
BillingContactExtensionText Populates based on Billing Name or can enter new
BillingContactFax Text Populates based on Billing Name or can enter new
BillingContactEmail Text Populates based on Billing Name or can enter new

Next, I created a subform (uQuoteDetails) to store Quote details:

QuoteID Autonumber
fkCustomerID Number Is not visible to user but links to parent table's
CustomerID field
QuoteDate Date/Time User entered
PromiseDate Date/Time User entered
ContractID Text User entered
Purchase Order Text User Selected from Drop Down (3-4 Choices)
Terms Text User Selected from Drop Down (3-4 Choices)
EnteredByFullName Text User entered
TotalPrice Currency Is not visible to user
Shipping Currency Is not visible to user
TaxRate Number Is not visible to user
TotalTaxes Currency Is not visible to user
FinalPrice Currency Is not visible to user
AdditionalNotes Text Is not visible to user

More on the latter fields later.

Next, I created a second subform (uParts) to hold part info:

PartID Autonumber
fkQuoteID Number Is not visible to user but links to subform's QuoteID field
Quantity Number User entered
PartNumDesc Number User Selected from Drop Down based on values contained in
Inventory_Part_040709 Table or can add a new part number and description
Cost Currency Populates based on PartNumDesc
Markup Number User entered %
UnitPrice Currency Calculated by: ([Quantity] * [Cost]) * (1 + [Markup])
Discount Number User entered %
CustPrice Currency Calculated by: [UnitPrice] * (1 - [Discount])


Finally, I setup the second part of the first subform in order to calculate
totals:

QuoteID Autonumber Is not visible to user
fkCustomerID Number Is not visible to user
QuoteDate Date/Time Is not visible to user
PromiseDate Date/Time Is not visible to user
ContractID Text Is not visible to user
Purchase Order Text Is not visible to user
Terms Text Is not visible to user
EnteredByFullName Text Is not visible to user
TotalPrice Currency Calculated by: Summing ALL of the CustPrice in all the
rows
Shipping Currency Entered by User
TaxRate Number Entered by User
TotalTaxes Currency Calculated by: ([TotalPrice] + [Shipping]) * (1 +
[TaxRate])
FinalPrice Currency Calculated by: ([TotalPrice] + [Shipping] + [TotalTaxes])
AdditionalNotes Text Entered by User


Can anyone help me create this from?

I TRULLY APPRECIATE ANY HELP YOU CAN PROVIDE.
Thanks.

 

[Crystal (strive4peace)]

Hi swatb98 (what is your name?)

No need to apologize for being new, we are here to help you. It would
be best, however, before you try to set up forms to make sure your data
is structured correctly. Do you have your relationships set up? It is
important to get your foundation set before you build your walls

For better understanding of the basics of Access, read this:

Access Basics by Crystal (Access MVP site)
http://www.AccessMVP.com/strive4peace
free 100-page tutorial that covers essentials in Access

then, when you are ready to do forms -- look at the main form/subform
section of Access Basics for some guidelines <smile>

it also appears that your totals can be calculated without using another
subform ...

Warm Regards,
Crystal
remote programming and training

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

*
have an awesome day
*

I apologize, I am new to Access. I need help executing the following concept.
When a customer calls to order parts, I would like to be able to create a
quote for the desired parts. I understand that this sets up as ONE customer
with MANY quotes and ONE quote with MANY parts.

So, I have setup a parent form (uCustomerBilling) with the following fields:

CustomerID Autonumber User Selects from Drop Down based on on
CustomerListMaster031909 Table
Equipment Model Text User Entered
Equipment Serial Number Text User Entered
Billing Name Text User Selected Drop Down or can enter new
Billing Address 01 Text Populates based on Billing Name or can enter new
Billing Address 02 Text Populates based on Billing Name or can enter new
Billing City Text Populates based on Billing Name or can
enter new
Billing StateRegion Text Populates based on Billing Name or can enter new
BillingZipPostal Text Populates based on Billing Name or can enter new
BillingCountry Text Populates based on Billing Name or can enter new
BillingContactFullNameText Populates based on Billing Name or can enter new
BillingContactPhone Text Populates based on Billing Name or can enter new
BillingContactExtensionText Populates based on Billing Name or can enter new
BillingContactFax Text Populates based on Billing Name or can enter new
BillingContactEmail Text Populates based on Billing Name or can enter new

Next, I created a subform (uQuoteDetails) to store Quote details:

QuoteID Autonumber
fkCustomerID Number Is not visible to user but links to parent table's
CustomerID field
QuoteDate Date/Time User entered
PromiseDate Date/Time User entered
ContractID Text User entered
Purchase Order Text User Selected from Drop Down (3-4 Choices)
Terms Text User Selected from Drop Down (3-4 Choices)
EnteredByFullName Text User entered
TotalPrice Currency Is not visible to user
Shipping Currency Is not visible to user
TaxRate Number Is not visible to user
TotalTaxes Currency Is not visible to user
FinalPrice Currency Is not visible to user
AdditionalNotes Text Is not visible to user

More on the latter fields later.

Next, I created a second subform (uParts) to hold part info:

PartID Autonumber
fkQuoteID Number Is not visible to user but links to subform's QuoteID field
Quantity Number User entered
PartNumDesc Number User Selected from Drop Down based on values contained in
Inventory_Part_040709 Table or can add a new part number and description
Cost Currency Populates based on PartNumDesc
Markup Number User entered %
UnitPrice Currency Calculated by: ([Quantity] * [Cost]) * (1 + [Markup])
Discount Number User entered %
CustPrice Currency Calculated by: [UnitPrice] * (1 - [Discount])


Finally, I setup the second part of the first subform in order to calculate
totals:

QuoteID Autonumber Is not visible to user
fkCustomerID Number Is not visible to user
QuoteDate Date/Time Is not visible to user
PromiseDate Date/Time Is not visible to user
ContractID Text Is not visible to user
Purchase Order Text Is not visible to user
Terms Text Is not visible to user
EnteredByFullName Text Is not visible to user
TotalPrice Currency Calculated by: Summing ALL of the CustPrice in all the
rows
Shipping Currency Entered by User
TaxRate Number Entered by User
TotalTaxes Currency Calculated by: ([TotalPrice] + [Shipping]) * (1 +
[TaxRate])
FinalPrice Currency Calculated by: ([TotalPrice] + [Shipping] + [TotalTaxes])
AdditionalNotes Text Entered by User


Can anyone help me create this from?

I TRULLY APPRECIATE ANY HELP YOU CAN PROVIDE.
Thanks.

 

[swatb98]

Crystal,

Thank you for your advice. I have looked through your document (and
downloaded it for later reference. Thanks for taking the time to make it);
however, I think I need just a bit more specificity.

Again, here's what I am envisioning:

(A) One table with many customers.
(B) One table with many parts.
(C) One parent form that contains customers' addresses and contact info
(with certain portions populated from the customers table.
(D) One subform that contains the Quote Details
(E) One subform that contains the parts that customers order (with certain
portions populated from the parts table.
(F) One subform (or part of the same parent form) that calculates prices and
totals
(G) One table that records all the data entered into the parent form and
subforms


So, a customer calls me and I issue a quote.
I pick up the phone and fill out parent form (C) based on inputs from table
(A).
I then fillout subform (D) followed by subform (E), which get inputs from
table (B).
The form would then calculate totals automatically in subform (F).
I can then analyze all of my quotes based on all the data contained in table
(G).

Does that make sense?

I feel like I have the parts and relationships down pat. I just need help
executing this process.

Any help you can provide is greatly appreciated.

Thanks,
Bernard

Hi swatb98 (what is your name?)

No need to apologize for being new, we are here to help you. It would
be best, however, before you try to set up forms to make sure your data
is structured correctly. Do you have your relationships set up? It is
important to get your foundation set before you build your walls

For better understanding of the basics of Access, read this:

Access Basics by Crystal (Access MVP site)
http://www.AccessMVP.com/strive4peace
free 100-page tutorial that covers essentials in Access

then, when you are ready to do forms -- look at the main form/subform
section of Access Basics for some guidelines <smile>

it also appears that your totals can be calculated without using another
subform ...

Warm Regards,
Crystal
remote programming and training

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

*
have an awesome day
*

I apologize, I am new to Access. I need help executing the following concept.
When a customer calls to order parts, I would like to be able to create a
quote for the desired parts. I understand that this sets up as ONE customer
with MANY quotes and ONE quote with MANY parts.

So, I have setup a parent form (uCustomerBilling) with the following fields:

CustomerID Autonumber User Selects from Drop Down based on on
CustomerListMaster031909 Table
Equipment Model Text User Entered
Equipment Serial Number Text User Entered
Billing Name Text User Selected Drop Down or can enter new
Billing Address 01 Text Populates based on Billing Name or can enter new
Billing Address 02 Text Populates based on Billing Name or can enter new
Billing City Text Populates based on Billing Name or can
enter new
Billing StateRegion Text Populates based on Billing Name or can enter new
BillingZipPostal Text Populates based on Billing Name or can enter new
BillingCountry Text Populates based on Billing Name or can enter new
BillingContactFullNameText Populates based on Billing Name or can enter new
BillingContactPhone Text Populates based on Billing Name or can enter new
BillingContactExtensionText Populates based on Billing Name or can enter new
BillingContactFax Text Populates based on Billing Name or can enter new
BillingContactEmail Text Populates based on Billing Name or can enter new

Next, I created a subform (uQuoteDetails) to store Quote details:

QuoteID Autonumber
fkCustomerID Number Is not visible to user but links to parent table's
CustomerID field
QuoteDate Date/Time User entered
PromiseDate Date/Time User entered
ContractID Text User entered
Purchase Order Text User Selected from Drop Down (3-4 Choices)
Terms Text User Selected from Drop Down (3-4 Choices)
EnteredByFullName Text User entered
TotalPrice Currency Is not visible to user
Shipping Currency Is not visible to user
TaxRate Number Is not visible to user
TotalTaxes Currency Is not visible to user
FinalPrice Currency Is not visible to user
AdditionalNotes Text Is not visible to user

More on the latter fields later.

Next, I created a second subform (uParts) to hold part info:

PartID Autonumber
fkQuoteID Number Is not visible to user but links to subform's QuoteID field
Quantity Number User entered
PartNumDesc Number User Selected from Drop Down based on values contained in
Inventory_Part_040709 Table or can add a new part number and description
Cost Currency Populates based on PartNumDesc
Markup Number User entered %
UnitPrice Currency Calculated by: ([Quantity] * [Cost]) * (1 + [Markup])
Discount Number User entered %
CustPrice Currency Calculated by: [UnitPrice] * (1 - [Discount])


Finally, I setup the second part of the first subform in order to calculate
totals:

QuoteID Autonumber Is not visible to user
fkCustomerID Number Is not visible to user
QuoteDate Date/Time Is not visible to user
PromiseDate Date/Time Is not visible to user
ContractID Text Is not visible to user
Purchase Order Text Is not visible to user
Terms Text Is not visible to user
EnteredByFullName Text Is not visible to user
TotalPrice Currency Calculated by: Summing ALL of the CustPrice in all the
rows
Shipping Currency Entered by User
TaxRate Number Entered by User
TotalTaxes Currency Calculated by: ([TotalPrice] + [Shipping]) * (1 +
[TaxRate])
FinalPrice Currency Calculated by: ([TotalPrice] + [Shipping] + [TotalTaxes])
AdditionalNotes Text Entered by User


Can anyone help me create this from?

I TRULLY APPRECIATE ANY HELP YOU CAN PROVIDE.
Thanks.

 

[Crystal (strive4peace)]

Hi Bernard,

you're welcome

your list was looking good until I got to E, F and G...

E. your Parts table will be used as a RowSource for the combobox to pick
a Part. You will store a PartID in the QuoteDetails and/or OrderDetails
table. You will also want to store price as prices may change. You do
NOT want to store other information about the part such as its
PartNumber or PartName as you can link to that anytime (and therefore
display it) and it should not change.

On Price, I am assuming that you can get parts from different suppliers
and they may have pricing structures -- so the price to the customer,
depending on whether it is set for a part or is a markup on your price,
or changes depending on quantity, may get tricky. If you have different
prices for Parts, you will also need Pricing structures.

F. you will want to do calculations on the subforms that have the data
you are calculating -- although those controls may not be visible if you
want to consolidate all the calculations into one place -- like on the
main form

G. "One table" - absolutely not. You have multiple tables listed and,
as a general rule, you should have just one table as the RecordSource
for each form or subform

"I feel like I have the parts and relationships down pat. "

forgive me, but I do not think that you do...

please list all the TABLES you see that you need... forget the forms for
now -- lets make sure the data structure and relationships, which are
the foundation, are good and strong before you build your house


Warm Regards,
Crystal
remote programming and training

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

Access Basics
http://www.AccessMVP.com/strive4peace
free 100-page tutorial that covers essentials in Access

*
have an awesome day
*

Crystal,

Thank you for your advice. I have looked through your document (and
downloaded it for later reference. Thanks for taking the time to make it);
however, I think I need just a bit more specificity.

Again, here's what I am envisioning:

(A) One table with many customers.
(B) One table with many parts.
(C) One parent form that contains customers' addresses and contact info
(with certain portions populated from the customers table.
(D) One subform that contains the Quote Details
(E) One subform that contains the parts that customers order (with certain
portions populated from the parts table.
(F) One subform (or part of the same parent form) that calculates prices and
totals
(G) One table that records all the data entered into the parent form and
subforms


So, a customer calls me and I issue a quote.
I pick up the phone and fill out parent form (C) based on inputs from table
(A).
I then fillout subform (D) followed by subform (E), which get inputs from
table (B).
The form would then calculate totals automatically in subform (F).
I can then analyze all of my quotes based on all the data contained in table
(G).

Does that make sense?

I feel like I have the parts and relationships down pat. I just need help
executing this process.

Any help you can provide is greatly appreciated.

Thanks,
Bernard

Hi swatb98 (what is your name?)

No need to apologize for being new, we are here to help you. It would
be best, however, before you try to set up forms to make sure your data
is structured correctly. Do you have your relationships set up? It is
important to get your foundation set before you build your walls

For better understanding of the basics of Access, read this:

Access Basics by Crystal (Access MVP site)
http://www.AccessMVP.com/strive4peace
free 100-page tutorial that covers essentials in Access

then, when you are ready to do forms -- look at the main form/subform
section of Access Basics for some guidelines <smile>

it also appears that your totals can be calculated without using another
subform ...

Warm Regards,
Crystal
remote programming and training

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

*
have an awesome day
*

I apologize, I am new to Access. I need help executing the following concept.
When a customer calls to order parts, I would like to be able to create a
quote for the desired parts. I understand that this sets up as ONE customer
with MANY quotes and ONE quote with MANY parts.

So, I have setup a parent form (uCustomerBilling) with the following fields:

CustomerID Autonumber User Selects from Drop Down based on on
CustomerListMaster031909 Table
Equipment Model Text User Entered
Equipment Serial Number Text User Entered
Billing Name Text User Selected Drop Down or can enter new
Billing Address 01 Text Populates based on Billing Name or can enter new
Billing Address 02 Text Populates based on Billing Name or can enter new
Billing City Text Populates based on Billing Name or can
enter new
Billing StateRegion Text Populates based on Billing Name or can enter new
BillingZipPostal Text Populates based on Billing Name or can enter new
BillingCountry Text Populates based on Billing Name or can enter new
BillingContactFullNameText Populates based on Billing Name or can enter new
BillingContactPhone Text Populates based on Billing Name or can enter new
BillingContactExtensionText Populates based on Billing Name or can enter new
BillingContactFax Text Populates based on Billing Name or can enter new
BillingContactEmail Text Populates based on Billing Name or can enter new

Next, I created a subform (uQuoteDetails) to store Quote details:

QuoteID Autonumber
fkCustomerID Number Is not visible to user but links to parent table's
CustomerID field
QuoteDate Date/Time User entered
PromiseDate Date/Time User entered
ContractID Text User entered
Purchase Order Text User Selected from Drop Down (3-4 Choices)
Terms Text User Selected from Drop Down (3-4 Choices)
EnteredByFullName Text User entered
TotalPrice Currency Is not visible to user
Shipping Currency Is not visible to user
TaxRate Number Is not visible to user
TotalTaxes Currency Is not visible to user
FinalPrice Currency Is not visible to user
AdditionalNotes Text Is not visible to user

More on the latter fields later.

Next, I created a second subform (uParts) to hold part info:

PartID Autonumber
fkQuoteID Number Is not visible to user but links to subform's QuoteID field
Quantity Number User entered
PartNumDesc Number User Selected from Drop Down based on values contained in
Inventory_Part_040709 Table or can add a new part number and description
Cost Currency Populates based on PartNumDesc
Markup Number User entered %
UnitPrice Currency Calculated by: ([Quantity] * [Cost]) * (1 + [Markup])
Discount Number User entered %
CustPrice Currency Calculated by: [UnitPrice] * (1 - [Discount])


Finally, I setup the second part of the first subform in order to calculate
totals:

QuoteID Autonumber Is not visible to user
fkCustomerID Number Is not visible to user
QuoteDate Date/Time Is not visible to user
PromiseDate Date/Time Is not visible to user
ContractID Text Is not visible to user
Purchase Order Text Is not visible to user
Terms Text Is not visible to user
EnteredByFullName Text Is not visible to user
TotalPrice Currency Calculated by: Summing ALL of the CustPrice in all the
rows
Shipping Currency Entered by User
TaxRate Number Entered by User
TotalTaxes Currency Calculated by: ([TotalPrice] + [Shipping]) * (1 +
[TaxRate])
FinalPrice Currency Calculated by: ([TotalPrice] + [Shipping] + [TotalTaxes])
AdditionalNotes Text Entered by User


Can anyone help me create this from?

I TRULLY APPRECIATE ANY HELP YOU CAN PROVIDE.
Thanks.

 

[swatb98]

Crystal,

Thanks for your candid response and your guidance. I am rightly defering to
your knowledge and expertise with Access, so, as requested, here are the
tables I think I need:

1) A table that contains all of the parts and their associated details (part
name, part number, cost, whether they're in stock, etc...). This table was
created in excel and has been imported into access.

2) A table that contains all customers and their associated details
(customer name, customer address). This table was created in excel and has
been imported into access.

3) A table that will contain all the following information (I am not sure
whether this info should be contained in one table or many tables--as you
said, I need help with these relationships):

Customer Name (drop down with Customer Table as the source)
Equipment Model
Equipment Serial Number
Billing Name
Billing Address 01
Billing Address 02
Billing City
Billing StateRegion
BillingZipPostal
BillingCountry
BillingContactFullName
BillingContactPhone
BillingContactExtension
BillingContactFax
BillingContactEmail

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

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

TotalPrice
Shipping
TaxRate
TotalTaxes
FinalPrice
Additional Notes


So, I have listed all the fields I can think of that are relevant to
creating the end product (feel free to add/delete).
To address your point about the prices changing: wouldn't I be able to
manually change the price of a part in the Parts Table so that all future
quotes would reflect this change?
To address your point about sums and calculations: I am all for having the
quote sum on the main form.

Again, thank you for your patience and guidance.
Bernard

Hi Bernard,

you're welcome

your list was looking good until I got to E, F and G...

E. your Parts table will be used as a RowSource for the combobox to pick
a Part. You will store a PartID in the QuoteDetails and/or OrderDetails
table. You will also want to store price as prices may change. You do
NOT want to store other information about the part such as its
PartNumber or PartName as you can link to that anytime (and therefore
display it) and it should not change.

On Price, I am assuming that you can get parts from different suppliers
and they may have pricing structures -- so the price to the customer,
depending on whether it is set for a part or is a markup on your price,
or changes depending on quantity, may get tricky. If you have different
prices for Parts, you will also need Pricing structures.

F. you will want to do calculations on the subforms that have the data
you are calculating -- although those controls may not be visible if you
want to consolidate all the calculations into one place -- like on the
main form

G. "One table" - absolutely not. You have multiple tables listed and,
as a general rule, you should have just one table as the RecordSource
for each form or subform

"I feel like I have the parts and relationships down pat. "

forgive me, but I do not think that you do...

please list all the TABLES you see that you need... forget the forms for
now -- lets make sure the data structure and relationships, which are
the foundation, are good and strong before you build your house


Warm Regards,
Crystal
remote programming and training

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

Access Basics
http://www.AccessMVP.com/strive4peace
free 100-page tutorial that covers essentials in Access

*
have an awesome day
*

Crystal,

Thank you for your advice. I have looked through your document (and
downloaded it for later reference. Thanks for taking the time to make it);
however, I think I need just a bit more specificity.

Again, here's what I am envisioning:

(A) One table with many customers.
(B) One table with many parts.
(C) One parent form that contains customers' addresses and contact info
(with certain portions populated from the customers table.
(D) One subform that contains the Quote Details
(E) One subform that contains the parts that customers order (with certain
portions populated from the parts table.
(F) One subform (or part of the same parent form) that calculates prices and
totals
(G) One table that records all the data entered into the parent form and
subforms


So, a customer calls me and I issue a quote.
I pick up the phone and fill out parent form (C) based on inputs from table
(A).
I then fillout subform (D) followed by subform (E), which get inputs from
table (B).
The form would then calculate totals automatically in subform (F).
I can then analyze all of my quotes based on all the data contained in table
(G).

Does that make sense?

I feel like I have the parts and relationships down pat. I just need help
executing this process.

Any help you can provide is greatly appreciated.

Thanks,
Bernard

Hi swatb98 (what is your name?)

No need to apologize for being new, we are here to help you. It would
be best, however, before you try to set up forms to make sure your data
is structured correctly. Do you have your relationships set up? It is
important to get your foundation set before you build your walls

For better understanding of the basics of Access, read this:

Access Basics by Crystal (Access MVP site)
http://www.AccessMVP.com/strive4peace
free 100-page tutorial that covers essentials in Access

then, when you are ready to do forms -- look at the main form/subform
section of Access Basics for some guidelines <smile>

it also appears that your totals can be calculated without using another
subform ...

Warm Regards,
Crystal
remote programming and training

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

*
have an awesome day
*


swatb98 wrote:
I apologize, I am new to Access. I need help executing the following concept.
When a customer calls to order parts, I would like to be able to create a
quote for the desired parts. I understand that this sets up as ONE customer
with MANY quotes and ONE quote with MANY parts.

So, I have setup a parent form (uCustomerBilling) with the following fields:

CustomerID Autonumber User Selects from Drop Down based on on
CustomerListMaster031909 Table
Equipment Model Text User Entered
Equipment Serial Number Text User Entered
Billing Name Text User Selected Drop Down or can enter new
Billing Address 01 Text Populates based on Billing Name or can enter new
Billing Address 02 Text Populates based on Billing Name or can enter new
Billing City Text Populates based on Billing Name or can
enter new
Billing StateRegion Text Populates based on Billing Name or can enter new
BillingZipPostal Text Populates based on Billing Name or can enter new
BillingCountry Text Populates based on Billing Name or can enter new
BillingContactFullNameText Populates based on Billing Name or can enter new
BillingContactPhone Text Populates based on Billing Name or can enter new
BillingContactExtensionText Populates based on Billing Name or can enter new
BillingContactFax Text Populates based on Billing Name or can enter new
BillingContactEmail Text Populates based on Billing Name or can enter new

Next, I created a subform (uQuoteDetails) to store Quote details:

QuoteID Autonumber
fkCustomerID Number Is not visible to user but links to parent table's
CustomerID field
QuoteDate Date/Time User entered
PromiseDate Date/Time User entered
ContractID Text User entered
Purchase Order Text User Selected from Drop Down (3-4 Choices)
Terms Text User Selected from Drop Down (3-4 Choices)
EnteredByFullName Text User entered
TotalPrice Currency Is not visible to user
Shipping Currency Is not visible to user
TaxRate Number Is not visible to user
TotalTaxes Currency Is not visible to user
FinalPrice Currency Is not visible to user
AdditionalNotes Text Is not visible to user

More on the latter fields later.

Next, I created a second subform (uParts) to hold part info:

PartID Autonumber
fkQuoteID Number Is not visible to user but links to subform's QuoteID field
Quantity Number User entered
PartNumDesc Number User Selected from Drop Down based on values contained in
Inventory_Part_040709 Table or can add a new part number and description
Cost Currency Populates based on PartNumDesc
Markup Number User entered %
UnitPrice Currency Calculated by: ([Quantity] * [Cost]) * (1 + [Markup])
Discount Number User entered %
CustPrice Currency Calculated by: [UnitPrice] * (1 - [Discount])


Finally, I setup the second part of the first subform in order to calculate
totals:

QuoteID Autonumber Is not visible to user
fkCustomerID Number Is not visible to user
QuoteDate Date/Time Is not visible to user
PromiseDate Date/Time Is not visible to user
ContractID Text Is not visible to user
Purchase Order Text Is not visible to user
Terms Text Is not visible to user
EnteredByFullName Text Is not visible to user
TotalPrice Currency Calculated by: Summing ALL of the CustPrice in all the
rows
Shipping Currency Entered by User
TaxRate Number Entered by User
TotalTaxes Currency Calculated by: ([TotalPrice] + [Shipping]) * (1 +
[TaxRate])
FinalPrice Currency Calculated by: ([TotalPrice] + [Shipping] + [TotalTaxes])
AdditionalNotes Text Entered by User


Can anyone help me create this from?

I TRULLY APPRECIATE ANY HELP YOU CAN PROVIDE.
Thanks.

 

[Crystal (strive4peace)]

Hi Bernard,

you're welcome

1. "whether they're in stock"

you should have additional tables if you are going to include inventory
control...

InventoryCount (for physical inventory counts)
- InvCountID, autonumber, PK
- PartID, long integer -- FK to Parts
- DateCount, date/time -- date inventory was taken

InvTransactions
- InvTranID, autonumber, PK
- PartID, long integer -- FK to Parts
- DateTran, date/time -- date of transaction
- Qty, number (long integer if you do not need decimal places) -- this
number will be positive for items put into inventory and negative for
items taken out of inventory
- etc

PK = Primary Key
FK = Foreign Key

2. "A table that contains all customers and their associated details
(customer name, customer address)"

when you import data from Excel, in order to take better advantage of
Access, it is a good idea to restructure your information. For
instance, a company may have two addresses (or more) -- ie, for billing
and shipping. You may also have multiple phone/fax numbers -- same with
email and websites.

you would have the following tables:

Customers (with CustomerID as the autonumber PK)

Addresses (with an AddrTypeID specified -- billing, shipping, or both)
Phones
Emails
Websites
Contacts -- this will either be a cross-reference table to link people
to companies or this will be a related table to specify contacts for a
given company as there may be more than one person you work with

watch my video tutorials for more information on this:

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

you can also download this database for ideas on storing contact
information:

Contacts -- Names, Addresses, Phones, eMail, Websites, Notes
http://www.utteraccess.com/forums/showflat.php?Cat=&Board=48&Number=1428638

you have to join UA and be signed in to download anything

3. I am assuming this is your Quotes table

"Customer Name"

you will store a CustomerID, not a name in the related table -- once
again, this is explained in the video tutorials.

Equipment Model
Equipment Serial Number

I would suggest a related table for CustomerEquipment with an autonumber
CustEqID and store a long integer CustEqID in the Quote table -- by
doing this, you would not also store the text fields

you would not put this in your Quotes table, it would be in a
QuoteDetails table (and I now realize that you SKIPPED the Quotes table
-- you need this for header information)

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

~~
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

~~~~~~~~~~~~~~~
"wouldn't I be able to manually change the price of a part in the Parts
Table so that all future quotes would reflect this change?"

yes, you will set the database up such that, when you do a new quote, it
will look up the current price -- that is why it is important to store
price when you use it, in case it changes.


Warm Regards,
Crystal
remote programming and training

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

Access Basics
http://www.AccessMVP.com/strive4peace
free 100-page tutorial that covers essentials in Access

*
have an awesome day
*

 

[swatb98]

Crystal,

Just to let you know, I am watching the turorials and following your
suggestions.
I will write back tomorrow with an update.

Thanks again for all of your help.

 

[swatb98]

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 sheet 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

 

[Crystal (strive4peace)]

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
*

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 sheet 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