Many, Many, Many........ One? I'm lost

[tedzbug]

Here is what i am trying to accomplish (i am kinda green on access but
learning fast)

I have tool quotes i want to start using access for so i can search
history and enter old ones too.

I have a Quote that has customer and contact on it.
A single quote can have multiple tools on it (not std stock items, most
always new numbers and descriptions)
But also a single tool will have multiple quoted quantities and a price
to go with each..........

I am having a hard time normalizing this correctly, i think i might be
making everything too complex as i have been working on this on and off
for a month or two.

 

[Graham Mandeno]

I suggest you have five tables: Customers, Quotes, QuoteItems, Products
(tools), and Prices.

They need to have the following skeletal structures:

Customers:
CustomerID (primary key)
CustomerName
... other customer info - address, email, phone, etc

Quotes:
QuoteNumber (PK)
CustomerID (foreign key to Customers)
QuoteDate
... other quote data - expiry date, status, etc

QuoteItems:
QuoteNumber (FK to Quotes)
ProductID (FK to Products)
Quantity
UnitPrice
Discount (if applicable)

Products:
ProductID (PK)
ProductName
... other non-price product data as required

Prices:
ProductID (FK to Products)
MinQuantity
UnitPrice
(you could make ProductID/MinQuantity a composite PK)

You need to consider that prices change from time to time, and you might
want to override the default price for a particular quote - say a special
price for the mother-in-law or something
For this reason, you should store a copy of the unit price in your quote
items table. This does not mean your structure is not normalised, as it is
meaningful to store such point-in-time data.

Note that there is no ItemTotal in QuoteItems, nor is there a QuoteTotal in
Quotes, because these can easily be calculated, and it is generally Not A
Good Thing to store calculated data. You can create a query, qryQuoteItems,
which contains all the fields from your QuoteItems table, plus an extra
calculated field:
ItemTotal: UnitPrice * Quantity * (1-Nz(Discount, 0))

To create a quote, you will want a form (based on Quotes) and a subform
(based on qryQuoteItems)

Now, all you need is a function to return you the current unit price, given
the ProductID and Quantity:

Public Function GetProductPrice(ProductID, Quantity) As Variant
Dim rs As DAO.Recordset
Dim sSql As String
If IsNumeric(ProductID) And IsNumeric(Quantity) Then
sSql = "SELECT TOP 1 UnitPrice FROM Prices" _
& " WHERE ProductID=" & ProductID _
& " AND MinQuantity<=" & Quantity _
& " ORDER BY MinQuantity DESC;"
Set rs = CurrentDb.OpenRecordset(sSql, dbOpenForwardOnly)
If rs.RecordCount > 0 Then GetProductPrice = rs!UnitPrice
rs.Close
End If
End Function

You can call this function in the AfterUpdate events of both ProductID and
Quantity on your subform:
UnitPrice = GetProductPrice(ProductID, Quantity)

I hope this is enough to get you going
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

 

[tedzbug]

This is a great start, thank you. The only thing i am still unsure of
is that if i have quote "a", Quote "a" might have tools "1,2&3" and
those might have quantities and prices i specify "5 @ $50, 10 @ $45, 20
@ $40" there will not be a specific price for any tool, it should
always be quantity prices and they are not based on a unit price they
are based on multiple other factors.

Thank you for your assistance

Ted Z

I suggest you have five tables: Customers, Quotes, QuoteItems, Products
(tools), and Prices.

They need to have the following skeletal structures:

Customers:
CustomerID (primary key)
CustomerName
... other customer info - address, email, phone, etc

Quotes:
QuoteNumber (PK)
CustomerID (foreign key to Customers)
QuoteDate
... other quote data - expiry date, status, etc

QuoteItems:
QuoteNumber (FK to Quotes)
ProductID (FK to Products)
Quantity
UnitPrice
Discount (if applicable)

Products:
ProductID (PK)
ProductName
... other non-price product data as required

Prices:
ProductID (FK to Products)
MinQuantity
UnitPrice
(you could make ProductID/MinQuantity a composite PK)

You need to consider that prices change from time to time, and you might
want to override the default price for a particular quote - say a special
price for the mother-in-law or something
For this reason, you should store a copy of the unit price in your quote
items table. This does not mean your structure is not normalised, as it is
meaningful to store such point-in-time data.

Note that there is no ItemTotal in QuoteItems, nor is there a QuoteTotal in
Quotes, because these can easily be calculated, and it is generally Not A
Good Thing to store calculated data. You can create a query, qryQuoteItems,
which contains all the fields from your QuoteItems table, plus an extra
calculated field:
ItemTotal: UnitPrice * Quantity * (1-Nz(Discount, 0))

To create a quote, you will want a form (based on Quotes) and a subform
(based on qryQuoteItems)

Now, all you need is a function to return you the current unit price, given
the ProductID and Quantity:

Public Function GetProductPrice(ProductID, Quantity) As Variant
Dim rs As DAO.Recordset
Dim sSql As String
If IsNumeric(ProductID) And IsNumeric(Quantity) Then
sSql = "SELECT TOP 1 UnitPrice FROM Prices" _
& " WHERE ProductID=" & ProductID _
& " AND MinQuantity<=" & Quantity _
& " ORDER BY MinQuantity DESC;"
Set rs = CurrentDb.OpenRecordset(sSql, dbOpenForwardOnly)
If rs.RecordCount > 0 Then GetProductPrice = rs!UnitPrice
rs.Close
End If
End Function

You can call this function in the AfterUpdate events of both ProductID and
Quantity on your subform:
UnitPrice = GetProductPrice(ProductID, Quantity)

I hope this is enough to get you going
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Here is what i am trying to accomplish (i am kinda green on access but
learning fast)

I have tool quotes i want to start using access for so i can search
history and enter old ones too.

I have a Quote that has customer and contact on it.
A single quote can have multiple tools on it (not std stock items, most
always new numbers and descriptions)
But also a single tool will have multiple quoted quantities and a price
to go with each..........

I am having a hard time normalizing this correctly, i think i might be
making everything too complex as i have been working on this on and off
for a month or two.

 

[tedzbug]

that qty i listed is example for tool 1

tools 2 & 3 will have different or same quantities and their own
specific multifactor based prices.

thanks

This is a great start, thank you. The only thing i am still unsure of
is that if i have quote "a", Quote "a" might have tools "1,2&3" and
those might have quantities and prices i specify "5 @ $50, 10 @ $45, 20
@ $40" there will not be a specific price for any tool, it should
always be quantity prices and they are not based on a unit price they
are based on multiple other factors.

Thank you for your assistance

Ted Z

I suggest you have five tables: Customers, Quotes, QuoteItems, Products
(tools), and Prices.

They need to have the following skeletal structures:

Customers:
CustomerID (primary key)
CustomerName
... other customer info - address, email, phone, etc

Quotes:
QuoteNumber (PK)
CustomerID (foreign key to Customers)
QuoteDate
... other quote data - expiry date, status, etc

QuoteItems:
QuoteNumber (FK to Quotes)
ProductID (FK to Products)
Quantity
UnitPrice
Discount (if applicable)

Products:
ProductID (PK)
ProductName
... other non-price product data as required

Prices:
ProductID (FK to Products)
MinQuantity
UnitPrice
(you could make ProductID/MinQuantity a composite PK)

You need to consider that prices change from time to time, and you might
want to override the default price for a particular quote - say a special
price for the mother-in-law or something
For this reason, you should store a copy of the unit price in your quote
items table. This does not mean your structure is not normalised, as it is
meaningful to store such point-in-time data.

Note that there is no ItemTotal in QuoteItems, nor is there a QuoteTotal in
Quotes, because these can easily be calculated, and it is generally Not A
Good Thing to store calculated data. You can create a query, qryQuoteItems,
which contains all the fields from your QuoteItems table, plus an extra
calculated field:
ItemTotal: UnitPrice * Quantity * (1-Nz(Discount, 0))

To create a quote, you will want a form (based on Quotes) and a subform
(based on qryQuoteItems)

Now, all you need is a function to return you the current unit price, given
the ProductID and Quantity:

Public Function GetProductPrice(ProductID, Quantity) As Variant
Dim rs As DAO.Recordset
Dim sSql As String
If IsNumeric(ProductID) And IsNumeric(Quantity) Then
sSql = "SELECT TOP 1 UnitPrice FROM Prices" _
& " WHERE ProductID=" & ProductID _
& " AND MinQuantity<=" & Quantity _
& " ORDER BY MinQuantity DESC;"
Set rs = CurrentDb.OpenRecordset(sSql, dbOpenForwardOnly)
If rs.RecordCount > 0 Then GetProductPrice = rs!UnitPrice
rs.Close
End If
End Function

You can call this function in the AfterUpdate events of both ProductID and
Quantity on your subform:
UnitPrice = GetProductPrice(ProductID, Quantity)

I hope this is enough to get you going

Graham Mandeno [Access MVP]
Auckland, New Zealand

 

[tedzbug]

Mainly making sure i am clear.........

I also want to be able to show each tool item and all qtys of each on
the quote form.

that qty i listed is example for tool 1

tools 2 & 3 will have different or same quantities and their own
specific multifactor based prices.

thanks

This is a great start, thank you. The only thing i am still unsure of
is that if i have quote "a", Quote "a" might have tools "1,2&3" and
those might have quantities and prices i specify "5 @ $50, 10 @ $45, 20
@ $40" there will not be a specific price for any tool, it should
always be quantity prices and they are not based on a unit price they
are based on multiple other factors.

Thank you for your assistance

Ted Z

On Jan 4, 6:58 pm, "Graham Mandeno" <[email protected]>
wrote:

I suggest you have five tables: Customers, Quotes, QuoteItems, Products
(tools), and Prices.
They need to have the following skeletal structures:
Customers:
CustomerID (primary key)
CustomerName
... other customer info - address, email, phone, etc
Quotes:
QuoteNumber (PK)
CustomerID (foreign key to Customers)
QuoteDate
... other quote data - expiry date, status, etc
QuoteItems:
QuoteNumber (FK to Quotes)
ProductID (FK to Products)
Quantity
UnitPrice
Discount (if applicable)
Products:
ProductID (PK)
ProductName
... other non-price product data as required
Prices:
ProductID (FK to Products)
MinQuantity
UnitPrice
(you could make ProductID/MinQuantity a composite PK)
You need to consider that prices change from time to time, and you might
want to override the default price for a particular quote - say a special
price for the mother-in-law or something
For this reason, you should store a copy of the unit price in your quote
items table. This does not mean your structure is not normalised, as it is
meaningful to store such point-in-time data.
Note that there is no ItemTotal in QuoteItems, nor is there a QuoteTotal in
Quotes, because these can easily be calculated, and it is generally Not A
Good Thing to store calculated data. You can create a query, qryQuoteItems,
which contains all the fields from your QuoteItems table, plus an extra
calculated field:
ItemTotal: UnitPrice * Quantity * (1-Nz(Discount, 0))
To create a quote, you will want a form (based on Quotes) and a subform
(based on qryQuoteItems)
Now, all you need is a function to return you the current unit price, given
the ProductID and Quantity:
Public Function GetProductPrice(ProductID, Quantity) As Variant
Dim rs As DAO.Recordset
Dim sSql As String
If IsNumeric(ProductID) And IsNumeric(Quantity) Then
sSql = "SELECT TOP 1 UnitPrice FROM Prices" _
& " WHERE ProductID=" & ProductID _
& " AND MinQuantity<=" & Quantity _
& " ORDER BY MinQuantity DESC;"
Set rs = CurrentDb.OpenRecordset(sSql, dbOpenForwardOnly)
If rs.RecordCount > 0 Then GetProductPrice = rs!UnitPrice
rs.Close
End If
End Function
You can call this function in the AfterUpdate events of both ProductID and
Quantity on your subform:
UnitPrice = GetProductPrice(ProductID, Quantity)
I hope this is enough to get you going
--
Good Luck!
Graham Mandeno [Access MVP]
Auckland, New Zealand
Here is what i am trying to accomplish (i am kinda green on access but
learning fast)
I have tool quotes i want to start using access for so i can search
history and enter old ones too.
I have a Quote that has customer and contact on it.
A single quote can have multiple tools on it (not std stock items, most
always new numbers and descriptions)
But also a single tool will have multiple quoted quantities and a price
to go with each..........
I am having a hard time normalizing this correctly, i think i might be
making everything too complex as i have been working on this on and off
for a month or two.

 

[Graham Mandeno]

Hi Ted

Do you mean that, in this example case, you could not buy 15 of tool 1, only
5, 10 or 20? In other words, you can purchase only those quantities listed
in the price table?

This would require some modification, but not major. I'll wait for
confirmation before I tell you how to go about it.
--

Graham Mandeno [Access MVP]
Auckland, New Zealand

This is a great start, thank you. The only thing i am still unsure of
is that if i have quote "a", Quote "a" might have tools "1,2&3" and
those might have quantities and prices i specify "5 @ $50, 10 @ $45, 20
@ $40" there will not be a specific price for any tool, it should
always be quantity prices and they are not based on a unit price they
are based on multiple other factors.

Thank you for your assistance

Ted Z

I suggest you have five tables: Customers, Quotes, QuoteItems, Products
(tools), and Prices.

They need to have the following skeletal structures:

Customers:
CustomerID (primary key)
CustomerName
... other customer info - address, email, phone, etc

Quotes:
QuoteNumber (PK)
CustomerID (foreign key to Customers)
QuoteDate
... other quote data - expiry date, status, etc

QuoteItems:
QuoteNumber (FK to Quotes)
ProductID (FK to Products)
Quantity
UnitPrice
Discount (if applicable)

Products:
ProductID (PK)
ProductName
... other non-price product data as required

Prices:
ProductID (FK to Products)
MinQuantity
UnitPrice
(you could make ProductID/MinQuantity a composite PK)

You need to consider that prices change from time to time, and you might
want to override the default price for a particular quote - say a special
price for the mother-in-law or something
For this reason, you should store a copy of the unit price in your quote
items table. This does not mean your structure is not normalised, as it
is
meaningful to store such point-in-time data.

Note that there is no ItemTotal in QuoteItems, nor is there a QuoteTotal
in
Quotes, because these can easily be calculated, and it is generally Not A
Good Thing to store calculated data. You can create a query,
qryQuoteItems,
which contains all the fields from your QuoteItems table, plus an extra
calculated field:
ItemTotal: UnitPrice * Quantity * (1-Nz(Discount, 0))

To create a quote, you will want a form (based on Quotes) and a subform
(based on qryQuoteItems)

Now, all you need is a function to return you the current unit price,
given
the ProductID and Quantity:

Public Function GetProductPrice(ProductID, Quantity) As Variant
Dim rs As DAO.Recordset
Dim sSql As String
If IsNumeric(ProductID) And IsNumeric(Quantity) Then
sSql = "SELECT TOP 1 UnitPrice FROM Prices" _
& " WHERE ProductID=" & ProductID _
& " AND MinQuantity<=" & Quantity _
& " ORDER BY MinQuantity DESC;"
Set rs = CurrentDb.OpenRecordset(sSql, dbOpenForwardOnly)
If rs.RecordCount > 0 Then GetProductPrice = rs!UnitPrice
rs.Close
End If
End Function

You can call this function in the AfterUpdate events of both ProductID
and
Quantity on your subform:
UnitPrice = GetProductPrice(ProductID, Quantity)

I hope this is enough to get you going
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Here is what i am trying to accomplish (i am kinda green on access but
learning fast)

I have tool quotes i want to start using access for so i can search
history and enter old ones too.

I have a Quote that has customer and contact on it.
A single quote can have multiple tools on it (not std stock items, most
always new numbers and descriptions)
But also a single tool will have multiple quoted quantities and a price
to go with each..........

I am having a hard time normalizing this correctly, i think i might be
making everything too complex as i have been working on this on and off
for a month or two.

 

[Graham Mandeno]

Hi Ted

Yes, these would be on the QuoteItems subform. The best way to select and
display the name of the tool/product is probably to use a combo box bound to
ProductID with its rowsource based on the Products table.
--
Graham Mandeno [Access MVP]
Auckland, New Zealand

Mainly making sure i am clear.........

I also want to be able to show each tool item and all qtys of each on
the quote form.

that qty i listed is example for tool 1

tools 2 & 3 will have different or same quantities and their own
specific multifactor based prices.

thanks

This is a great start, thank you. The only thing i am still unsure of
is that if i have quote "a", Quote "a" might have tools "1,2&3" and
those might have quantities and prices i specify "5 @ $50, 10 @ $45, 20
@ $40" there will not be a specific price for any tool, it should
always be quantity prices and they are not based on a unit price they
are based on multiple other factors.

Thank you for your assistance

Ted Z

On Jan 4, 6:58 pm, "Graham Mandeno" <[email protected]>
wrote:

I suggest you have five tables: Customers, Quotes, QuoteItems,
Products
(tools), and Prices.

They need to have the following skeletal structures:

Customers:
CustomerID (primary key)
CustomerName
... other customer info - address, email, phone, etc

Quotes:
QuoteNumber (PK)
CustomerID (foreign key to Customers)
QuoteDate
... other quote data - expiry date, status, etc

QuoteItems:
QuoteNumber (FK to Quotes)
ProductID (FK to Products)
Quantity
UnitPrice
Discount (if applicable)

Products:
ProductID (PK)
ProductName
... other non-price product data as required

Prices:
ProductID (FK to Products)
MinQuantity
UnitPrice
(you could make ProductID/MinQuantity a composite PK)

You need to consider that prices change from time to time, and you
might
want to override the default price for a particular quote - say a
special
price for the mother-in-law or something
For this reason, you should store a copy of the unit price in your
quote
items table. This does not mean your structure is not normalised, as
it is
meaningful to store such point-in-time data.

Note that there is no ItemTotal in QuoteItems, nor is there a
QuoteTotal in
Quotes, because these can easily be calculated, and it is generally
Not A
Good Thing to store calculated data. You can create a query,
qryQuoteItems,
which contains all the fields from your QuoteItems table, plus an
extra
calculated field:
ItemTotal: UnitPrice * Quantity * (1-Nz(Discount, 0))

To create a quote, you will want a form (based on Quotes) and a
subform
(based on qryQuoteItems)

Now, all you need is a function to return you the current unit price,
given
the ProductID and Quantity:

Public Function GetProductPrice(ProductID, Quantity) As Variant
Dim rs As DAO.Recordset
Dim sSql As String
If IsNumeric(ProductID) And IsNumeric(Quantity) Then
sSql = "SELECT TOP 1 UnitPrice FROM Prices" _
& " WHERE ProductID=" & ProductID _
& " AND MinQuantity<=" & Quantity _
& " ORDER BY MinQuantity DESC;"
Set rs = CurrentDb.OpenRecordset(sSql, dbOpenForwardOnly)
If rs.RecordCount > 0 Then GetProductPrice = rs!UnitPrice
rs.Close
End If
End Function

You can call this function in the AfterUpdate events of both
ProductID and
Quantity on your subform:
UnitPrice = GetProductPrice(ProductID, Quantity)

I hope this is enough to get you going

Graham Mandeno [Access MVP]
Auckland, New Zealand

messagenews:[email protected]...

Here is what i am trying to accomplish (i am kinda green on access
but
learning fast)

I have tool quotes i want to start using access for so i can search
history and enter old ones too.

I have a Quote that has customer and contact on it.
A single quote can have multiple tools on it (not std stock items,
most
always new numbers and descriptions)
But also a single tool will have multiple quoted quantities and a
price
to go with each..........

I am having a hard time normalizing this correctly, i think i might
be
making everything too complex as i have been working on this on and
off
for a month or two.

 

[tedzbug]

Yes that is the point, When someone sends a quote request to us they
ask for specific quanitites and these are what we quote to them.

Thanks
Ted

Hi Ted

Yes, these would be on the QuoteItems subform. The best way to select and
display the name of the tool/product is probably to use a combo box bound to
ProductID with its rowsource based on the Products table.
--
Graham Mandeno [Access MVP]
Auckland, New Zealand

Mainly making sure i am clear.........

I also want to be able to show each tool item and all qtys of each on
the quote form.

that qty i listed is example for tool 1
tools 2 & 3 will have different or same quantities and their own
specific multifactor based prices.
thanks
This is a great start, thank you. The only thing i am still unsure of
is that if i have quote "a", Quote "a" might have tools "1,2&3" and
those might have quantities and prices i specify "5 @ $50, 10 @ $45, 20
@ $40" there will not be a specific price for any tool, it should
always be quantity prices and they are not based on a unit price they
are based on multiple other factors.
Thank you for your assistance
Ted Z
On Jan 4, 6:58 pm, "Graham Mandeno" <[email protected]>
wrote:
I suggest you have five tables: Customers, Quotes, QuoteItems,
Products
(tools), and Prices.
They need to have the following skeletal structures:
Customers:
CustomerID (primary key)
CustomerName
... other customer info - address, email, phone, etc
Quotes:
QuoteNumber (PK)
CustomerID (foreign key to Customers)
QuoteDate
... other quote data - expiry date, status, etc
QuoteItems:
QuoteNumber (FK to Quotes)
ProductID (FK to Products)
Quantity
UnitPrice
Discount (if applicable)
Products:
ProductID (PK)
ProductName
... other non-price product data as required
Prices:
ProductID (FK to Products)
MinQuantity
UnitPrice
(you could make ProductID/MinQuantity a composite PK)
You need to consider that prices change from time to time, and you
might
want to override the default price for a particular quote - say a
special
price for the mother-in-law or something
For this reason, you should store a copy of the unit price in your
quote
items table. This does not mean your structure is not normalised, as
it is
meaningful to store such point-in-time data.
Note that there is no ItemTotal in QuoteItems, nor is there a
QuoteTotal in
Quotes, because these can easily be calculated, and it is generally
Not A
Good Thing to store calculated data. You can create a query,
qryQuoteItems,
which contains all the fields from your QuoteItems table, plus an
extra
calculated field:
ItemTotal: UnitPrice * Quantity * (1-Nz(Discount, 0))
To create a quote, you will want a form (based on Quotes) and a
subform
(based on qryQuoteItems)
Now, all you need is a function to return you the current unit price,
given
the ProductID and Quantity:
Public Function GetProductPrice(ProductID, Quantity) As Variant
Dim rs As DAO.Recordset
Dim sSql As String
If IsNumeric(ProductID) And IsNumeric(Quantity) Then
sSql = "SELECT TOP 1 UnitPrice FROM Prices" _
& " WHERE ProductID=" & ProductID _
& " AND MinQuantity<=" & Quantity _
& " ORDER BY MinQuantity DESC;"
Set rs = CurrentDb.OpenRecordset(sSql, dbOpenForwardOnly)
If rs.RecordCount > 0 Then GetProductPrice = rs!UnitPrice
rs.Close
End If
End Function
You can call this function in the AfterUpdate events of both
ProductID and
Quantity on your subform:
UnitPrice = GetProductPrice(ProductID, Quantity)
I hope this is enough to get you going
--
Good Luck!
Graham Mandeno [Access MVP]
Auckland, New Zealand
message
Here is what i am trying to accomplish (i am kinda green on access
but
learning fast)
I have tool quotes i want to start using access for so i can search
history and enter old ones too.
I have a Quote that has customer and contact on it.
A single quote can have multiple tools on it (not std stock items,
most
always new numbers and descriptions)
But also a single tool will have multiple quoted quantities and a
price
to go with each..........
I am having a hard time normalizing this correctly, i think i might
be
making everything too complex as i have been working on this on and
off
for a month or two.

 

[Graham Mandeno]

Sorry, Ted, I think you misunderstood my question.

Can you take an order for 12 or 13 or 14, or any number of Left-Handed
Widgets, or do you sell Left-Handed Widgets only in set quantities - say 5,
10 or 20?
--
Graham Mandeno [Access MVP]
Auckland, New Zealand

Yes that is the point, When someone sends a quote request to us they
ask for specific quanitites and these are what we quote to them.

Thanks
Ted

Hi Ted

Yes, these would be on the QuoteItems subform. The best way to select
and
display the name of the tool/product is probably to use a combo box bound
to
ProductID with its rowsource based on the Products table.
--
Graham Mandeno [Access MVP]
Auckland, New Zealand

Mainly making sure i am clear.........

I also want to be able to show each tool item and all qtys of each on
the quote form.

that qty i listed is example for tool 1

tools 2 & 3 will have different or same quantities and their own
specific multifactor based prices.

On Jan 5, 11:24 am, "tedzbug" <[email protected]> wrote:

This is a great start, thank you. The only thing i am still unsure
of
is that if i have quote "a", Quote "a" might have tools "1,2&3" and
those might have quantities and prices i specify "5 @ $50, 10 @ $45,
20
@ $40" there will not be a specific price for any tool, it should
always be quantity prices and they are not based on a unit price
they
are based on multiple other factors.

Thank you for your assistance

Ted Z

On Jan 4, 6:58 pm, "Graham Mandeno" <[email protected]>
wrote:

I suggest you have five tables: Customers, Quotes, QuoteItems,
Products
(tools), and Prices.

They need to have the following skeletal structures:

Customers:
CustomerID (primary key)
CustomerName
... other customer info - address, email, phone, etc

Quotes:
QuoteNumber (PK)
CustomerID (foreign key to Customers)
QuoteDate
... other quote data - expiry date, status, etc

QuoteItems:
QuoteNumber (FK to Quotes)
ProductID (FK to Products)
Quantity
UnitPrice
Discount (if applicable)

Products:
ProductID (PK)
ProductName
... other non-price product data as required

Prices:
ProductID (FK to Products)
MinQuantity
UnitPrice
(you could make ProductID/MinQuantity a composite PK)

You need to consider that prices change from time to time, and you
might
want to override the default price for a particular quote - say a
special
price for the mother-in-law or something
For this reason, you should store a copy of the unit price in your
quote
items table. This does not mean your structure is not normalised,
as
it is
meaningful to store such point-in-time data.

Note that there is no ItemTotal in QuoteItems, nor is there a
QuoteTotal in
Quotes, because these can easily be calculated, and it is
generally
Not A
Good Thing to store calculated data. You can create a query,
qryQuoteItems,
which contains all the fields from your QuoteItems table, plus an
extra
calculated field:
ItemTotal: UnitPrice * Quantity * (1-Nz(Discount, 0))

To create a quote, you will want a form (based on Quotes) and a
subform
(based on qryQuoteItems)

Now, all you need is a function to return you the current unit
price,
given
the ProductID and Quantity:

Public Function GetProductPrice(ProductID, Quantity) As Variant
Dim rs As DAO.Recordset
Dim sSql As String
If IsNumeric(ProductID) And IsNumeric(Quantity) Then
sSql = "SELECT TOP 1 UnitPrice FROM Prices" _
& " WHERE ProductID=" & ProductID _
& " AND MinQuantity<=" & Quantity _
& " ORDER BY MinQuantity DESC;"
Set rs = CurrentDb.OpenRecordset(sSql, dbOpenForwardOnly)
If rs.RecordCount > 0 Then GetProductPrice = rs!UnitPrice
rs.Close
End If
End Function

You can call this function in the AfterUpdate events of both
ProductID and
Quantity on your subform:
UnitPrice = GetProductPrice(ProductID, Quantity)

I hope this is enough to get you going

Graham Mandeno [Access MVP]
Auckland, New Zealand

messagenews:[email protected]...

Here is what i am trying to accomplish (i am kinda green on
access
but
learning fast)

I have tool quotes i want to start using access for so i can
search
history and enter old ones too.

I have a Quote that has customer and contact on it.
A single quote can have multiple tools on it (not std stock
items,
most
always new numbers and descriptions)
But also a single tool will have multiple quoted quantities and
a
price
to go with each..........

I am having a hard time normalizing this correctly, i think i
might
be
making everything too complex as i have been working on this on
and
off
for a month or two.

 

[tedzbug]

i can, but most of the time what we quote is what they order......

Sorry, Ted, I think you misunderstood my question.

Can you take an order for 12 or 13 or 14, or any number of Left-Handed
Widgets, or do you sell Left-Handed Widgets only in set quantities - say 5,
10 or 20?
--
Graham Mandeno [Access MVP]
Auckland, New Zealand

Yes that is the point, When someone sends a quote request to us they
ask for specific quanitites and these are what we quote to them.

Thanks
Ted

Hi Ted
Yes, these would be on the QuoteItems subform. The best way to select
and
display the name of the tool/product is probably to use a combo box bound
to
ProductID with its rowsource based on the Products table.
--
Graham Mandeno [Access MVP]
Auckland, New Zealand
message
Mainly making sure i am clear.........
I also want to be able to show each tool item and all qtys of each on
the quote form.
that qty i listed is example for tool 1
tools 2 & 3 will have different or same quantities and their own
specific multifactor based prices.
thanks
This is a great start, thank you. The only thing i am still unsure
of
is that if i have quote "a", Quote "a" might have tools "1,2&3" and
those might have quantities and prices i specify "5 @ $50, 10 @ $45,
20
@ $40" there will not be a specific price for any tool, it should
always be quantity prices and they are not based on a unit price
they
are based on multiple other factors.
Thank you for your assistance
Ted Z
On Jan 4, 6:58 pm, "Graham Mandeno" <[email protected]>
wrote:
I suggest you have five tables: Customers, Quotes, QuoteItems,
Products
(tools), and Prices.
They need to have the following skeletal structures:
Customers:
CustomerID (primary key)
CustomerName
... other customer info - address, email, phone, etc
Quotes:
QuoteNumber (PK)
CustomerID (foreign key to Customers)
QuoteDate
... other quote data - expiry date, status, etc
QuoteItems:
QuoteNumber (FK to Quotes)
ProductID (FK to Products)
Quantity
UnitPrice
Discount (if applicable)
Products:
ProductID (PK)
ProductName
... other non-price product data as required
Prices:
ProductID (FK to Products)
MinQuantity
UnitPrice
(you could make ProductID/MinQuantity a composite PK)
You need to consider that prices change from time to time, and you
might
want to override the default price for a particular quote - say a
special
price for the mother-in-law or something
For this reason, you should store a copy of the unit price in your
quote
items table. This does not mean your structure is not normalised,
as
it is
meaningful to store such point-in-time data.
Note that there is no ItemTotal in QuoteItems, nor is there a
QuoteTotal in
Quotes, because these can easily be calculated, and it is
generally
Not A
Good Thing to store calculated data. You can create a query,
qryQuoteItems,
which contains all the fields from your QuoteItems table, plus an
extra
calculated field:
ItemTotal: UnitPrice * Quantity * (1-Nz(Discount, 0))
To create a quote, you will want a form (based on Quotes) and a
subform
(based on qryQuoteItems)
Now, all you need is a function to return you the current unit
price,
given
the ProductID and Quantity:
Public Function GetProductPrice(ProductID, Quantity) As Variant
Dim rs As DAO.Recordset
Dim sSql As String
If IsNumeric(ProductID) And IsNumeric(Quantity) Then
sSql = "SELECT TOP 1 UnitPrice FROM Prices" _
& " WHERE ProductID=" & ProductID _
& " AND MinQuantity<=" & Quantity _
& " ORDER BY MinQuantity DESC;"
Set rs = CurrentDb.OpenRecordset(sSql, dbOpenForwardOnly)
If rs.RecordCount > 0 Then GetProductPrice = rs!UnitPrice
rs.Close
End If
End Function
You can call this function in the AfterUpdate events of both
ProductID and
Quantity on your subform:
UnitPrice = GetProductPrice(ProductID, Quantity)
I hope this is enough to get you going
--
Good Luck!
Graham Mandeno [Access MVP]
Auckland, New Zealand
message
Here is what i am trying to accomplish (i am kinda green on
access
but
learning fast)
I have tool quotes i want to start using access for so i can
search
history and enter old ones too.
I have a Quote that has customer and contact on it.
A single quote can have multiple tools on it (not std stock
items,
most
always new numbers and descriptions)
But also a single tool will have multiple quoted quantities and
a
price
to go with each..........
I am having a hard time normalizing this correctly, i think i
might
be
making everything too complex as i have been working on this on
and
off
for a month or two.

 

[Graham Mandeno]

Still not the answer I wanted, but I think I understand you

We are talking about quantity pricing, not fixed size packs.

So, let's say you have Tool 1, which you sell for $50 each.

If a customer orders 5-9 of them, the price is reduced to $45 each.
For an order of between 10 and 19, the price is reduced to $40.
For 20 or more, the price is $35 each.

Referring back to my first post, your Prices table would have four records:

ProductID MinQuantity UnitPrice
1 0 $50
1 5 $45
1 10 $40
1 20 $35

The GetProductPrice function I gave you will look up the unit price
appropriate to the ProductID and Quantity in your order.

If you select ProductID 1 and enter a required quantity of 12, the function
will find the first price fir that product with a MinQuantity less than or
equal to 12 (10) and will return a UnitPrice of $40.

The calculated ItemTotal field will be $480 (12 x $40).

If you add a Discount value of 10% (0.1) the calculated value will change to
$432.

I hope this is what you want and is clear now
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

i can, but most of the time what we quote is what they order......

Sorry, Ted, I think you misunderstood my question.

Can you take an order for 12 or 13 or 14, or any number of Left-Handed
Widgets, or do you sell Left-Handed Widgets only in set quantities - say
5,
10 or 20?
--
Graham Mandeno [Access MVP]
Auckland, New Zealand

Yes that is the point, When someone sends a quote request to us they
ask for specific quanitites and these are what we quote to them.

On Jan 5, 2:33 pm, "Graham Mandeno" <[email protected]>
wrote:
Hi Ted

Yes, these would be on the QuoteItems subform. The best way to select
and
display the name of the tool/product is probably to use a combo box
bound
to
ProductID with its rowsource based on the Products table.

messagenews:[email protected]...

Mainly making sure i am clear.........

I also want to be able to show each tool item and all qtys of each
on
the quote form.

that qty i listed is example for tool 1

tools 2 & 3 will have different or same quantities and their own
specific multifactor based prices.

On Jan 5, 11:24 am, "tedzbug" <[email protected]> wrote:

This is a great start, thank you. The only thing i am still
unsure
of
is that if i have quote "a", Quote "a" might have tools "1,2&3"
and
those might have quantities and prices i specify "5 @ $50, 10 @
$45,
20
@ $40" there will not be a specific price for any tool, it
should
always be quantity prices and they are not based on a unit price
they
are based on multiple other factors.

Thank you for your assistance

Ted Z

On Jan 4, 6:58 pm, "Graham Mandeno"
<[email protected]>
wrote:

I suggest you have five tables: Customers, Quotes, QuoteItems,
Products
(tools), and Prices.

They need to have the following skeletal structures:

Customers:
CustomerID (primary key)
CustomerName
... other customer info - address, email, phone, etc

Quotes:
QuoteNumber (PK)
CustomerID (foreign key to Customers)
QuoteDate
... other quote data - expiry date, status, etc

QuoteItems:
QuoteNumber (FK to Quotes)
ProductID (FK to Products)
Quantity
UnitPrice
Discount (if applicable)

Products:
ProductID (PK)
ProductName
... other non-price product data as required

Prices:
ProductID (FK to Products)
MinQuantity
UnitPrice
(you could make ProductID/MinQuantity a composite PK)

You need to consider that prices change from time to time, and
you
might
want to override the default price for a particular quote - say
a
special
price for the mother-in-law or something
For this reason, you should store a copy of the unit price in
your
quote
items table. This does not mean your structure is not
normalised,
as
it is
meaningful to store such point-in-time data.

Note that there is no ItemTotal in QuoteItems, nor is there a
QuoteTotal in
Quotes, because these can easily be calculated, and it is
generally
Not A
Good Thing to store calculated data. You can create a query,
qryQuoteItems,
which contains all the fields from your QuoteItems table, plus
an
extra
calculated field:
ItemTotal: UnitPrice * Quantity * (1-Nz(Discount, 0))

To create a quote, you will want a form (based on Quotes) and a
subform
(based on qryQuoteItems)

Now, all you need is a function to return you the current unit
price,
given
the ProductID and Quantity:

Public Function GetProductPrice(ProductID, Quantity) As Variant
Dim rs As DAO.Recordset
Dim sSql As String
If IsNumeric(ProductID) And IsNumeric(Quantity) Then
sSql = "SELECT TOP 1 UnitPrice FROM Prices" _
& " WHERE ProductID=" & ProductID _
& " AND MinQuantity<=" & Quantity _
& " ORDER BY MinQuantity DESC;"
Set rs = CurrentDb.OpenRecordset(sSql, dbOpenForwardOnly)
If rs.RecordCount > 0 Then GetProductPrice = rs!UnitPrice
rs.Close
End If
End Function

You can call this function in the AfterUpdate events of both
ProductID and
Quantity on your subform:
UnitPrice = GetProductPrice(ProductID, Quantity)

I hope this is enough to get you going

Graham Mandeno [Access MVP]
Auckland, New Zealand

messagenews:[email protected]...

Here is what i am trying to accomplish (i am kinda green on
access
but
learning fast)

I have tool quotes i want to start using access for so i can
search
history and enter old ones too.

I have a Quote that has customer and contact on it.
A single quote can have multiple tools on it (not std stock
items,
most
always new numbers and descriptions)
But also a single tool will have multiple quoted quantities
and
a
price
to go with each..........

I am having a hard time normalizing this correctly, i think i
might
be
making everything too complex as i have been working on this
on
and
off
for a month or two.