does this sql capability exist

[ddungan]

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

 

[ddungan]

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

I'm using Access 2000 on Windows 2000 5.00.2195 service pack 4

 

[Powderfinger]

Yes, but it would be a giant SQL statement with a lot of ORs and
IsNulls

strSQL = "SELECT * FROM tblTable Where (( [Field1] =
[forms]![MyForm]![CORE_PART] ) OR ([forms]![MyForm]![CORE_PART] Is Null))
AND [Field2] = [forms]![MyForm]![ADAPTER_CONFIG] ) OR
([forms]![MyForm]![ADAPTER_CONFIG] Is Null)) ...............etc

 

[ddungan]

Yes, but it would be a giant SQL statement with a lot of ORs and
IsNulls

strSQL = "SELECT * FROM tblTable Where (( [Field1] =
[forms]![MyForm]![CORE_PART] ) OR ([forms]![MyForm]![CORE_PART] Is Null))
AND [Field2] = [forms]![MyForm]![ADAPTER_CONFIG] ) OR
([forms]![MyForm]![ADAPTER_CONFIG] Is Null)) ...............etc

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.

Dan Dungan

Thanks Powderfinger. I'll work on this.

 

[Guest]

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

 

[Powderfinger]

Another option is to pass a filter to the form/report when opening. Putting
the names of controls and forms in a query can turn out to be problematic,
although it does work either way.
Here's an example:

Private Sub PrintitButton_Click()

Dim strCriteria As String
strCriteria = "True"

If Not IsNull(Me.Field1) Then
strCriteria = strCriteria & " AND [Field1] = " & Me.Field1
End If
If Not IsNull(Me.Field2) Then
strCriteria = strCriteria & " AND [Field2] = " & Me.Field2
End If
If Not IsNull(Me.Field3) Then
strCriteria = strCriteria & " AND [Field3] = " & Me.Field3
End If
..
..
..
..

DoCmd.OpenReport "rptMyReport", acViewNormal, , strCriteria

or

DoCmd.OpenForm "frmMyFormName", , strCriteria
'if you are opening a form with the filter

End Sub

Yes, but it would be a giant SQL statement with a lot of ORs and
IsNulls

strSQL = "SELECT * FROM tblTable Where (( [Field1] =
[forms]![MyForm]![CORE_PART] ) OR ([forms]![MyForm]![CORE_PART] Is Null))
AND [Field2] = [forms]![MyForm]![ADAPTER_CONFIG] ) OR
([forms]![MyForm]![ADAPTER_CONFIG] Is Null)) ...............etc

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.

Dan Dungan

Thanks Powderfinger. I'll work on this.

 

[ddungan]

Hi Steve,

Thanks for your responses.

I've been thinking about your questions. I'll reply back when I have
more information about the business rules.

Thanks,

Dan

 

[onedaywhen]

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?

CREATE PROCEDURE GetEmployeeNames (
name_type VARCHAR(5) = 'Last'
)
AS
SELECT E1.EmployeeID, name_type, SWITCH(
name_type = 'Last', E1.LastName,
name_type = 'First', E1.FirstName,
) AS name_value
FROM Employees AS E1;

Kinda daft example, though <g>.

Jamie.

--