Access query grid query

I have a query which delivers the data but i dont know how to make the query so that where the cc has slight variants i.e 2401 vs 2400 that I only get 1 value returned. my variance is 5.

I know how to calculate if the variance is in range ie abs x-y >=5

I dont know how to get the grouping correct with this data I would like it to show (here i manually filtered 1 of the 2 out)

this way I get the cc's for the xc90 vor vehicle.

ClientName      CC      StrLink
Abi      2400      XC90 VOR******   or it could show the 2401 and not the 2400
Abi      2521      XC90 VOR
Abi      2922      XC90 VOR
Abi      3192      XC90 VOR
Abi      4414      XC90 VOR
Cap            XC90 VOR
Glass      2400      XC90 VOR
Glass      2521      XC90 VOR
Glass      2922      XC90 VOR
Glass      3192      XC90 VOR
Smmt      2521      XC90 VOR
Smmt      2922      XC90 VOR
Smmt      3192      XC90 VOR
Tvi      0      XC90 VOR





output currently
SELECT QryXC90.ClientName, QryXC90.CC, QryXC90.StrLink
FROM QryXC90
GROUP BY QryXC90.ClientName, QryXC90.CC, QryXC90.StrLink
HAVING (((QryXC90.StrLink)="XC90 VOR"));

Open in new window

PeterBaileyUkAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
als315Connect With a Mentor Commented:
You can also round cc value with function like this:
Public Function round_cc(CC As Variant) As Variant
Dim i As Double
If IsNull(CC) Then
    round_cc = Null
Else
    i = CC / 10
    round_cc = CLng(i) * 10
End If
End Function

Open in new window

0
 
hnasrCommented:
Show the input data, and the required output.
If possible upload a sample database.
0
 
PeterBaileyUkAuthor Commented:
Ive attached a small db qrygroupcc so dont open with your mobile.if you look at client name abi or glass it each has 2 cc value one of 2401 and other 2400.

an ideal query should show but without changing the client data field

ClientName      CC      StrLink
Abi      2400      XC90 VOR
ee.mdb
0
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
aikimarkCommented:
if your variance is 5 or less then you will need something like this:
(Abs(CC - 2400) <= 5)
0
 
PeterBaileyUkAuthor Commented:
Variance wasnt the question I want 2400 and 2401 to be grouped together as one. I think it needs a query in a query to group on the min values but not sure.
0
 
Gustav BrockCIOCommented:
You could modify like this to round down by variance:

SELECT 
    QryXC90.ClientName, 
    Int([CC]/20)*20 AS CCGroup, 
    QryXC90.StrLink
FROM 
    QryXC90
GROUP BY 
    QryXC90.ClientName, 
    Int([CC]/20)*20, 
    QryXC90.StrLink
HAVING 
    QryXC90.StrLink="XC90 VOR";

Open in new window

/gustav
0
 
hnasrCommented:
Try this, run Qery1
SELECT QryXC90.ClientName, QryXC90.StrLink, Min(QryXC90.CC) AS MinOfCC
FROM QryXC90
GROUP BY QryXC90.ClientName, QryXC90.StrLink, Left([CC]+5,Len([CC])-1)
HAVING (((QryXC90.ClientName)="ABI") AND ((QryXC90.StrLink)="XC90 VOR"));

Open in new window

Modify criteria if needed.
ee-2.mdb
0
 
aikimarkCommented:
@PeterBaileyUk

We've been looking at your singular example of 2400 and 2401.  Since you've stated that you need a "variance of 5", does that mean you will accept any value between -5 and +5 of 2400 (or whatever the CC value)?

if so, try the following variation on your original query.  I am assuming that you have some other table (called CCtable in the example) that contains other values that might be eligible.
SELECT QryXC90.ClientName, QryXC90.CC, QryXC90.StrLink
FROM QryXC90 Inner Join CCtable On thejoiningcriteria
WHERE (QryXC90.StrLink="XC90 VOR")
And CCtable.CC Between QryXC90.CC-5 and QryXC90.CC+5
GROUP BY QryXC90.ClientName, QryXC90.CC, QryXC90.StrLink

Open in new window

0
 
PeterBaileyUkAuthor Commented:
yes exactly that, that was this example.
0
 
PeterBaileyUkAuthor Commented:
thank you.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.