Query Question - +/- Criteria 1

[tbassngal]

Hi all,

Just wondering - is it possible to create a new query field based upon data from another field using criteria of +/- ??? Now, I know that this doesn't work but there has to be a way. Example:

I have a field of data that is titled "Length" and say the value for that field is 48 on one record. When I query, I would like to pull records +2 (50) or -2 (46)... does that make sense? Sort of like a net range...

Not knowing what each records value is... this poses somewhat of a problem...

Thank you in advance for your help! Tina

 

[PHV]

does that make sense?
Not for me :-(
Could you please post some input samples and expected result ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[jsteph]

Without defining a specific set of ranges, you could be returning huge amounts of duplicates--ie,all records with the value 48 would show up on all records with values 46, 47, 48, 49, and 50.

I know what you're getting at, but it's not a practical thing without defining groups in which to corrall the data without duplicating it.
--Jim

 

[jsteph]

tbassngal,
Here is an example of what I mean:
Make a table called Table1, with 3 fields:
Recid (autonumber)
SomeVal (Long Int)
SomeText (Text, just for the heck of it)

See the below sql:

SELECT a.recid, a.SomeVal, a.SomeText
FROM Table1 a INNER JOIN Table1 b ON (a.SomeVal >= b.SomeVal -2 and a.SomeVal <= b.SomeVal + 2);

This gives sort of a semi-cartisean product.
--Jim

 

[PHV]

This gives sort of a semi-cartisean product
[pedantic]
In fact this is called a theta join
[/pedantic]

 

[tbassngal]

jsteph, you are right... but this is a request that was made of me. I am almost looking to get duplicate values. This is not my table... this is a co-worker who is taking a stab at trying to do something else. Basically - there are looking for products within a range of the same length - give or take a value of 2 (+/- 2). So, bad example but - if there is a piece of steel cut to 48 inches - we want all the steel in our database to come back within +/- 2. I know this is confusing and I am not so sure about this either, but thank you for all your help!!!

 

[jsteph]

tbassngal,
Use my example and put your fields/tables in. In my original Select, I only put fields from the 'a' table, you'd want to list the 'b' as well.
--Jim

 

[jsteph]

tbassngal,
Let me clarify--if you just want to run the query each time with a different single value with the +/- range--then it's much, much easier--just use the range you want in the criteria. But if it's where you want every record to be listed with it's range group, then use my example.
--Jim

 

[Iashia06]

I have an MS Access db. I am using data access pages to allow users to create/update records. I have three diffeerent pages. My first page is fine. Everything works great. It's purpose is to define a project. I have a separate page for each department however, and these pages are based on queries. My second page, Tracking, allows the users to enter weekly updates on the status of the project.
What the program needs to do is insert a NEW record each time the tracking data gets updated. However, based on the table design, the program should not create a new Tracking ID, it should retain the current Tracking ID and increment the Tracking Sequence by 1. Both fields make up the primary key for the table. I am trying to do this via VB Script. I am having serious problems. The code I'm using is below:

<SCRIPT language=vbscript event=onclick for=TrackingNavSave>
<!--
Dim dbLocation, strSQL, prmProjID, objConn, cat1


Set objConn = CreateObject("ADODB.Connection")
objConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=\\intraweb\ProjectTracking$\ProjectTracking.mdb"


strSQL = "INSERT INTO Tracking (Tracking_ID, Tracking_Seq, Project_ID, " & _
"Project_Seq, Project_Name, Department, Dept_Head, Dept_Head_Phone, " & _
"Project_Manager, Manager_Phone, Priority, Status, Percent_Complete, " & _
"Issues, Next_Steps, Report_Week, Achievement_Level, User_Name, " & _
"Warranty_Period, Project_Description, Milestone_1, Milestone_2, " & _
"Milestone_3, Milestone_4, Milestone_5, Milestone_Status_1, " & _
"Milestone_Status_2, Milestone_Status_3, Milestone_Status_4, " & _
"Milestone_Status_5, Milestone_Due_Date_1, Milestone_Due_Date_2, " & _
"Milestone_Due_Date_3, Milestone_Due_Date_4, Milestone_Due_Date_5) " & _
"VALUES (Tracking_ID.value, Tracking_Seq.value, Project_ID.value, " & _
"Project_Seq.value, Project_Name.value, Department.value, Dept_Head.value, " & _
"Dept_Head_Phone.value, Project_Manager.value, Manager_Phone.value, " & _
"Priority.value, Status.value, Percent_Complete.value, Issues.value, " & _
"Next_Steps.value, Report_Week.value, Achievement_Level.value, " & _
"User_Name.value, Warranty_Period.value, Project_Description.value, " & _
"Milestone_1.value, Milestone_2.value, Milestone_3.value, Milestone_4.value, " & _
"Milestone_5.value, Milestone_Status_1.value, Milestone_Status_2.value, " & _"Milestone_Status_3.value, Milestone_Status_4.value, Milestone_Status_5.value, " & _
"Milestone_Due_Date_1.value, Milestone_Due_Date_2.value, " & _
"Milestone_Due_Date_3.value, Milestone_Due_Date_4.value, Milestone_Due_Date_5.value)"

Set objConn = CreateObject ("ADODB.Command")
objConn.CommandText = strSQL
Set cat1 = CreateObject("ADOX.Catalog")
cat1.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=\\intraweb\ProjectTracking$\ProjectTracking.mdb"
Set prmProjID = objConn.CreateParameter(Project_ID, 19, 1, 4)
objConn.Parameters.Append prmProjID
Tracking_Seq.value = Tracking_Seq.value + 1
objConn.Execute strSQL

Set objConn = Nothing

-->
</SCRIPT>

Please help. I'm having trouble with defining the parameters for the query.

 

[lespaul]

first, you should start a new thread, but prior to doing that, you need to read the fundamentals document linked below. Your table is not normalized.

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[willybgw]

In the query design grid make a new field ck:abs[fld1]-[fld2]. In criteria for this field put 2.

willybgw