Access query grid query

Posted on 2016-11-05
Last Modified: 2016-11-05
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
GROUP BY QryXC90.ClientName, QryXC90.CC, QryXC90.StrLink
HAVING (((QryXC90.StrLink)="XC90 VOR"));

Open in new window

Question by:PeterBaileyUk
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
  • 2
  • +2
LVL 30

Expert Comment

ID: 41875217
Show the input data, and the required output.
If possible upload a sample database.

Author Comment

ID: 41875292
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
LVL 45

Expert Comment

ID: 41875309
if your variance is 5 or less then you will need something like this:
(Abs(CC - 2400) <= 5)
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Author Comment

ID: 41875317
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.
LVL 40

Accepted Solution

als315 earned 500 total points
ID: 41875333
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
    i = CC / 10
    round_cc = CLng(i) * 10
End If
End Function

Open in new window

LVL 50

Expert Comment

by:Gustav Brock
ID: 41875347
You could modify like this to round down by variance:

    Int([CC]/20)*20 AS CCGroup, 
    QryXC90.StrLink="XC90 VOR";

Open in new window

LVL 30

Expert Comment

ID: 41875465
Try this, run Qery1
SELECT QryXC90.ClientName, QryXC90.StrLink, Min(QryXC90.CC) AS MinOfCC
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.
LVL 45

Expert Comment

ID: 41875485

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


Author Comment

ID: 41875509
yes exactly that, that was this example.

Author Closing Comment

ID: 41875513
thank you.

Featured Post

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Access Runtime Error '3070' 5 50
Access lists formating 8 48
SQL Server 2005 running VERY slow on Virtual Machine 18 66
usage of ms/excell 3d maps in ms/access 2016 6 42
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

732 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question