Solved

Access 2003 Query not totalling simliar data

Posted on 2014-03-24
8
38 Views
Last Modified: 2015-07-30
I am using the following query:

SELECT tMain.FrameLine, tMain.FrameModel, Count(tMain.FrameModel) AS CountOfFrameModel, tMain.FrameOWF
FROM tMain
WHERE (((tMain.Office)=[Forms]![FReportSelect]![LocSelect]) AND ((tMain.FrameLine)=[Forms]![FReportSelect]![cFrameLine]) AND ((tMain.FrameOWF)=False) AND ((tMain.Status)<>"Cancelled")) OR (((tMain.FrameLine)=[Forms]![FReportSelect]![cFrameLine]) AND ((tMain.FrameOWF)=False) AND ((tMain.Status)<>"Cancelled") AND (([Forms]![FReportSelect]![LocSelect])="ALL"))
GROUP BY tMain.FrameLine, tMain.FrameModel, tMain.FrameOWF, tMain.Status
ORDER BY tMain.FrameModel;

Open in new window


For the most part its pulling up data correctly but for some reason it is not grouping on the same model.  Its not doing it for all models but just some times.  I can't figure out why given its the same exact model.

Is there anyting in the SQL that might be causing this?

Thank you.
0
Comment
Question by:thandel
  • 3
  • 3
  • 2
8 Comments
 
LVL 7

Expert Comment

by:COACHMAN99
ID: 39950991
if you select DISTINCT models, do you get repeating rows that look similar?
0
 
LVL 11

Expert Comment

by:John_Vidmar
ID: 39951085
You are also grouping by tMain.Status, but not displaying it... this would affect groupings
0
 

Author Comment

by:thandel
ID: 39952391
Thanks

Coachman, using distinct didn't have any change in results

John, I set status to be displayed but didn't have any change in results either.
0
 
LVL 7

Expert Comment

by:COACHMAN99
ID: 39954921
My reason for using DISTINCT was to identify the reason for multiple models.
if you select DISTINCT models, do you get repeating models that look similar?
only select models, no other fields, and see if there are multiple instances of similar models.
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:thandel
ID: 39955039
If I follow your question, when selecting distinct the results are multipul models on the report of the same exact model in the querry.

For example of the model "BOTTLE ROCKET 40" I am getting one row in the query with a countofframemodel of 1 and in another row with model "BOTTLE ROCKET 40" with a countofframemodel of 3.

They are both exactly the same model.

In the query I have some models with a  count of 6 but are only on a single row.
0
 
LVL 7

Expert Comment

by:COACHMAN99
ID: 39955065
I dont understand what you are saying. The query I suggested had one field - model; no counts etc.
Maybe attach an xls with the results so I can see.
0
 
LVL 11

Accepted Solution

by:
John_Vidmar earned 500 total points
ID: 39956688
He probably wants to know how clean is your data-field, i.e., do you have trailing spaces in model:

SELECT DISTINCT '#' + FrameModel + '#' FROM tMain
0
 

Author Comment

by:thandel
ID: 39957771
Thank you, the valaues entered are trimmed. I would attach a query but now when I run the issue is gone?????

Not sure how to proceed now, or why is behaving differently.  Odd, a repair is run daily... still seems OK this morning.  What is the best way to close / issue points on this?
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

762 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now