Access query not getting correct results

Question

I have an Access database that imports data from Excel. The database does a comparison between two tables. I do want to fuss with changing the structure of the data coming from Excel--e.g. there are no primary keys in the Access tables. When I do a query I do not get the correct results. I am keeping this as simple as can be.

Spec table fields: Dates--date format, Hours--number format
Stats table fields: Dates--date format, Cell--number format, Texting--number format

I have tried all 3 join properties on the Dates field and anytime I repeat the date in the Spec table with the same number of hours my query gives me quadrupling results.

Here is one of my attempts:

SELECT Spec.Dates, Spec.Hours, Stats.Dates, Stats.Cell, Stats.Texting
FROM Spec RIGHT JOIN Stats ON Spec.Dates = Stats.Dates
GROUP BY Spec.Dates, Spec.Hours, Stats.Dates, Stats.Cell, Stats.Texting;

I hope this is clear--I can attach a sample database if needed.

A query is a request for data results, for action on data, or for both. You can use a query to answer a simple question, to perform calculations, to combine data from different tables, or even to add, change, or delete table data. Queries that you use to retrieve data from a table or to make calculations are called select queries. Queries that add, change, or delete data are called action queries. — Jean Miller
You need to be able to uniquely identify a match - dates and hous may be duplicated so you need more than that. i.d. Employee ID or Name or Unit or ... — Wayne G. Dunn
I didn't ask what is a query :) I'm asking what is your SQL query that is not working. You didn't post the SQL query in your question. — sstan
SELECT Spec.Dates, Spec.Hours, Stats.Dates, Stats.Cell, Stats.Texting FROM Spec RIGHT JOIN Stats ON Spec.Dates = Stats.Dates GROUP BY Spec.Dates, Spec.Hours, Stats.Dates, Stats.Cell, Stats.Texting; — Jean Miller

SunKnight0 SunKnight0 · Accepted Answer · 2016-05-25T20:27:48

Try this:

SELECT Spec.Dates, Spec.Hours, FIRST(Stats.Cell) AS Cell, FIRST(Stats.Texting) AS Texting
FROM Spec RIGHT JOIN Stats ON Spec.Dates = Stats.Dates
GROUP BY Spec.Dates, Spec.Hours

This is unlikely what you are looking for but it may point you into the direction of what is going wrong. maybe if you can show us a set of records and results, both the actual and the expected ones I could get closer to a real answer.

For example:

Spec table:
5/5/2016 4:00
5/5/2016 5:00

Stats table:
5/5/2016 'Cell A' 'Texting A'
5/5/2016 'Cell B' 'texting B'

Would indeed result in 4 separate result lines for your query with all 4 combinations of the above 2x2 records. As far as I can tell there is no data in your database/import to get anything different (for example to only tie the 4:00 time to the Cell A/Texting A stat).

Access query not getting correct results

1 Answers