RE: does this sql capability exist

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Hi Dan,

I'm not quite sure what you are trying to do, but I can think or a couple
more ways to dynamically create the SQL statement.

If I understand right, you have fields in "tblCorePart" that are named
"1-9", "10-19", etc??? If so, you need to rethink your table structure. That
is the way you might set up a spreadsheet, but it is the wrongway for a
database.

For example, what happens if you need to have a price for 1-25 items instead
of 1-9 items for a new product. You might say "That will never happen".
Famous last words... <g>

I would set up the table "tblCorePart" something like this:

Field Name: Data Type:
lngCorePartID Autonumber (PK)
lngPartNumber Long Int (FK)
Quan_Min Integer
Quan_Max Integer
curPrice Currency


The "Parts" table would be like:

Field Name: Data Type:
lngPartID Autonumber (PK)
txtPartNumber Text
txtPartDesc Text
(other fields)



BTW, your first quanity is 9 items and all the rest are multiples of ten
items. I would have thought the groups would be 1-10, 11 20, 21-50 , ...
(just curious)

You might want to read up on database normalization. I keep a couple of
books nearby to refer to when setting up new tables.


Now for Question 1: "dynamic SQL". Using VBA, you could build the SQL using:

a series of 10 IF() statements,
a listbox and loop thru the selected items or
give the textboxes a common name (Quan1, Quan2,...) and loop from 1 -10


Question 2: I would have to answer "It depends". Are the prices time/date
sensetive? Do you need a historical record of the prices? What are the
business rules? Do you save the quoted price with in the quote record(s)? Do
you convert the quote to a PO or create a separate PO later? Does the set up
fee change - by customer, by product, quanity, time of year?????


HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


"ddungan@xxxxxxxxxxxxxxxxxxx" wrote:

Hi,

I have two questions:

1. Is there a way to dynamically choose which fields are included in
an sql statement based on if there is a value entered in text boxes?

I have 10 text boxes that represent 10 of the fields in my table
tblCorePart. These text boxes are labeled with quantity ranges.

These are the ten fields:

Table: tblPriceListCore

Field Name: Data Sample: Description:
ID Primary
Key
CORE_PART 217 Part Number
ADAPTER_CONFIG R
1-9 $10.47 Price for 1-9
items
10-19 $ 9.70 Price for 10-19
items
20-49 $ 9.47 Price for 20-49
items
50-99 $ 7.46 etc.
100-249 $ 5.98 etc.
250-499 $ 4.81 etc.
500-999 $ 4.25 etc.
1000-2499 $ 3.67 etc.
2500-4999 $ 3.33 etc.
5000 up $3.17 etc.

2. I need to use the value of the text box in a calculation for a
price mark up. Where should I store the value?

So after a customer service agent enters the part number in the form
frmBuildQuote. They enter the actual quantity in a text box. The agent
may enter as many as 4 quantities to quote. I need to divide the set
up fee by the actual quantity and add that to the quoted price.

I actually have many more questions, but I had to start somewhere.

I'm a new access developer. I just got this job, and I would truly
appreciate discussing this with someone.

Thanks,

Dan Dungan


.

Relevant Pages

  • Re: Now that the dust has settled... will Steve find the PC of his dreams?
    ... I started by checking out the new Conroe boxes from Intel. ... Athlon X2 5000+ in an AM2 socket, with a price that reflects AMD's ... with 8 hot-swap SATA drives in front. ...
    (comp.sys.ibm.pc.hardware.chips)
  • Now that the dust has settled... will Steve find the PC of his dreams?
    ... I started by checking out the new Conroe boxes from Intel. ... I have to give credit to the marketing department at Intel. ... Next idea was that AMD had lowered prices on the X2 4800+ and 5000+. ... Athlon X2 5000+ in an AM2 socket, with a price that reflects AMD's ...
    (comp.sys.ibm.pc.hardware.chips)
  • Re: Unable to reset Combo Box Data Field Format.
    ... Regards to mixed data types and the combo boxes - ... controls from the target SQL statement. ... > either selecting values from the combo box row source or by typing them> it ... > based on other combo box selections of fields in the DB. ...
    (microsoft.public.access.forms)
  • Re: Tape is Far More Expensive the DASD? (Was: State of the Mainframe - News Article)
    ... Regarding the licenses: *Some* of them are optional, but if you don't have the licenses then you don't have all the features you described. ... However you get 90% of features for fraction of the price. ... Enterprise Storage functions for cheap disk is charged at Enterprise price ... for TrueCopy and Shadowimage compared to using UVM, as you are still going to have to buy the licensed software for those boxes in order to have those features. ...
    (bit.listserv.ibm-main)
  • Re: Grateful Deads fateful blunder
    ... the box set run was sold out. ... people to the torrent sites. ... and the music only should have been available at a cheaper price. ... I think the main "blunder" was in selling the boxes ...
    (rec.music.gdead)