Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


Access 2003 Query not totalling simliar data

Posted on 2014-03-24
Medium Priority
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.
Question by:thandel
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
  • 3
  • 3
  • 2

Expert Comment

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

Expert Comment

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

Author Comment

ID: 39952391

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.
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.


Expert Comment

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.

Author Comment

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.

Expert Comment

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.
LVL 11

Accepted Solution

John_Vidmar earned 2000 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

Author Comment

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?

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

650 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