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.
