thandel
asked on
Counting simliar items in a query result
I have a table (tMain) of sales... one entry for each sale. In this table is the model (FrameModel) and an office (Office, currently just testing for all offices in the table.) I would like to run a query that will pull the max similar model sold and display their count and also find their percentage (based on the total number of FrameModels sold) . I am using the following query to try to pull this and a date range based on a form for the user, (Basically trying to find models that have sold over 1 to find out "top model" sellers.
Its returning results but I know for sure that there are other fame models not even showing up that have many sales (over 10)
SELECT tMain.DOS, tMain.FrameModel, Count(tMain.FrameModel) AS CountOfFrameModel, tMain.FrameLine, tMain.Office
FROM tMain
GROUP BY tMain.DOS, tMain.FrameModel, tMain.FrameLine, tMain.Office
HAVING (((tMain.DOS) Between [Forms]![FReportSelect]![D
ORDER BY Count(tMain.FrameModel) DESC;
Thank you.
Its returning results but I know for sure that there are other fame models not even showing up that have many sales (over 10)
SELECT tMain.DOS, tMain.FrameModel, Count(tMain.FrameModel) AS CountOfFrameModel, tMain.FrameLine, tMain.Office
FROM tMain
GROUP BY tMain.DOS, tMain.FrameModel, tMain.FrameLine, tMain.Office
HAVING (((tMain.DOS) Between [Forms]![FReportSelect]
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi
Pcelba, yes I see all the frame models in that date range for the given office. (123 of them found)
Aikimark - query works but is showing a count of 2 for all models when I know its not accurate as some have more than 2
Pcelba, yes I see all the frame models in that date range for the given office. (123 of them found)
Aikimark - query works but is showing a count of 2 for all models when I know its not accurate as some have more than 2
ASKER
Crystal getting an error with your query: cannot have aggregarte function where clause
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I can't see the data nor the form. The problem is most likely one of those two places.
ASKER
HI Pcelba... its a date range:
Between [Forms]![FReportSelect]![D
Between [Forms]![FReportSelect]![D
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks let me implement this....
ASKER
thank and sorry for the delay on this.... so I ran it but some of the models are not being reporting correctly. I've attached sample data. for example if you run with a date range of 1/1/18 - 5/20/2018 the Model "Utrecht" is showing as qty 4 but when I count manually its 7. Other models have the same miscount error.
Thoughts?
Thoughts?
ASKER
Sorry forgot to upload the file after selecting.. here it is.
Sample.xls
Sample.xls
Manual count shows 8 rows in above file so you should first make sure what file/table are you querying.
Where are your data stored? In Access database or in XLS file?
Where are your data stored? In Access database or in XLS file?
ASKER
in access database
ASKER
Hi any thoughts about why the query is not pulling up data fully?
You did not post the final version of your query. It would also help to post the accdb database with sample data (tMain table should be enough in this db).
The XLS file is not sufficient to tell what's wrong because your count (7) is different from my count (8) so we are even not sure whether are you looking at the same file.
The XLS file is not sufficient to tell what's wrong because your count (7) is different from my count (8) so we are even not sure whether are you looking at the same file.
ASKER
Sorry I didn't see you needed my query:
SELECT Count(tMain.FrameModel) AS CountOfFrameModel, tMain.FrameModel, tMain.FrameLine, tMain.JobType
FROM tMain
WHERE (((tMain.DOS) Between [Forms]![FReportSelect]![D
GROUP BY tMain.FrameModel, tMain.FrameLine, tMain.JobType
HAVING (((Count(tMain.FrameModel)
SELECT Count(tMain.FrameModel) AS CountOfFrameModel, tMain.FrameModel, tMain.FrameLine, tMain.JobType
FROM tMain
WHERE (((tMain.DOS) Between [Forms]![FReportSelect]![D
GROUP BY tMain.FrameModel, tMain.FrameLine, tMain.JobType
HAVING (((Count(tMain.FrameModel)
The query has some parts duplicated in WHERE and HEAVING clause. Why? OTOH, this should not make problem but I would recommend to fix it.
As you are not providing sample database you should do two things:
1) Display [DateStart] and [DateEnd] inside the application to ensure you are working with expected values. Optionally you may add the two form fields into the column list in the SELECT command
2) Execute the query in MS Access directly - it will allow to review the result set
As you are not providing sample database you should do two things:
1) Display [DateStart] and [DateEnd] inside the application to ensure you are working with expected values. Optionally you may add the two form fields into the column list in the SELECT command
2) Execute the query in MS Access directly - it will allow to review the result set
ASKER
I have the having as I have a count going to ensure that only a model of more than (1) is on the report... I didn't want single count models to be included as I consider anything with a count of more than (1) a "top" model.
This is OK and I understand it as it is working fine for you.
But remember you have
HAVING condition OR condition
so why to have two exactly same conditions in HAVING?
If you would need more specific help then we would need data sample from you. Simply create a sample accdb database containing just the data you would like to query and the expected result.
But remember you have
HAVING condition OR condition
so why to have two exactly same conditions in HAVING?
If you would need more specific help then we would need data sample from you. Simply create a sample accdb database containing just the data you would like to query and the expected result.
ASKER
Thank you.
Many good hints were provided in this question.
The difference can be in the date interpretation - American vs. Canadian date may cause the difference etc.
In other words: Are you sure there is over 10 sales for given DOS and FrameModel?