Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 60
  • Last Modified:

Access 2003 Query not totalling simliar data

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
thandel
Asked:
thandel
  • 3
  • 3
  • 2
1 Solution
 
COACHMAN99Commented:
if you select DISTINCT models, do you get repeating rows that look similar?
0
 
John_VidmarCommented:
You are also grouping by tMain.Status, but not displaying it... this would affect groupings
0
 
thandelAuthor Commented:
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
Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

 
COACHMAN99Commented:
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
 
thandelAuthor Commented:
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
 
COACHMAN99Commented:
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
 
John_VidmarCommented:
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
 
thandelAuthor Commented:
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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now