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

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

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

On Jan 5, 2:33 pm, "Graham Mandeno" <Graham.Mand...@xxxxxxxxxxxxx>
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.
--
Graham Mandeno [Access MVP]
Auckland, New Zealand

"tedzbug" <tedzill...@xxxxxxxxx> wrote in messagenews:1168016063.261964.108630@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

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.

On Jan 5, 11:26 am, "tedzbug" <tedzill...@xxxxxxxxx> wrote:
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

On Jan 5, 11:24 am, "tedzbug" <tedzill...@xxxxxxxxx> 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" <Graham.Mand...@xxxxxxxxxxxxx>
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

"tedzbug" <tedzill...@xxxxxxxxx> wrote in
messagenews:1167945441.581868.303390@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

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.

.

Relevant Pages

  • Re: Many, Many, Many........ One? Im lost
    ... If a customer orders 5-9 of them, the price is reduced to $45 each. ... ProductID MinQuantity UnitPrice ... ask for specific quanitites and these are what we quote to them. ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Many, Many, Many........ One? Im lost
    ... always be quantity prices and they are not based on a unit price they ... CustomerID ... ProductID ... want to override the default price for a particular quote - say a special ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Many, Many, Many........ One? Im lost
    ... these would be on the QuoteItems subform. ... ProductID with its rowsource based on the Products table. ... the quote form. ... always be quantity prices and they are not based on a unit price they ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Many, Many, Many........ One? Im lost
    ... always be quantity prices and they are not based on a unit price they ... CustomerID ... ProductID ... want to override the default price for a particular quote - say a special ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Many, Many, Many........ One? Im lost
    ... @ $40" there will not be a specific price for any tool, ... CustomerID ... ProductID ... want to override the default price for a particular quote - say a special ...
    (microsoft.public.access.tablesdbdesign)