Query that has no duplicates in one field

[Fasopus]

Im pretty new to access and databases in general and I dont really know what I'm doing. I have a database that will track peoples punch in and out times for a warehouse and im trying to make a query that brings up the information for the latest set of entries (ie: The previous days). However when I run it it gives me multiple values for each persons alias when there should only be one. I dont know how I can stick the query into here except in SQL mode, hopefully I just need to tweak my query:


SELECT DISTINCTROW Performance.Score, Max(Performance.Date) AS [Most Recent Date], [Warehouse Info].Hours, [Warehouse Info].Alias, Performance.Hours
FROM Calculations, Performance, [Warehouse Info]
WHERE ((([Warehouse Info].Alias)=[Performance].[ID] And ([Warehouse Info].Alias)=[Warehouse Info].[Alias]))
GROUP BY Performance.Score, [Warehouse Info].Hours, [Warehouse Info].Alias, Performance.Hours;

 

[jzwp22]

Welcome to AWF!

I would first use a query to get the most recent performance date by person.

SELECT Performance.personID?, Max(Performance.Date) AS [Most Recent Date]
FROM Performance
GROUP BY Performance.personID?

You mentioned that the table holds punch in/out of people, but I did not see a field that relates to a person, so I just represented that above as personID. You will have to substitute your corresponding field name

I would then create another query that joins the above query back to the performance table (joining via both the personID and date fields) and pull the other fields you need from the performance table (Score and hours I assume).

Now you would use that query to join (somehow) to your warehouse info table. You had a calculations table in your query, but you do not pull any fields from it, so I don't see a need for it in the query.

Also, you should not be using the word "date" as a field name; it is a reserved word in Access.

 

[MSAccessRookie]

Im pretty new to access and databases in general and I dont really know what I'm doing. I have a database that will track peoples punch in and out times for a warehouse and im trying to make a query that brings up the information for the latest set of entries (ie: The previous days). However when I run it it gives me multiple values for each persons alias when there should only be one. I dont know how I can stick the query into here except in SQL mode, hopefully I just need to tweak my query:


SELECT DISTINCTROW Performance.Score, Max(Performance.Date) AS [Most Recent Date], [Warehouse Info].Hours, [Warehouse Info].Alias
FROM Calculations, Performance, [Warehouse Info]
WHERE ((([Warehouse Info].Alias)=[Performance].[ID] And ([Warehouse Info].Alias)=[Warehouse Info].[Alias]))
GROUP BY Performance.Score, [Warehouse Info].Hours, [Warehouse Info].Alias;

The first issue that I noticed is that your FROM statement is the general SQL Standard, and not the specific type recoommended for Access. The preferred Access format would be something like the following:

SELECT {Something}
FROM ((Table1 INNER JOIN Table2 ON Table1.{KEY1} = Table2.{KEY1}) {INNER JOIN Table3 ON Table1.{KEY2} = Table3.{KEY2})
​

The second issue that I noticed is that you have three Tables, and Only two of them ( [Warehouse Info] and [Performance] ) are mentioned in the WHERE statements. The third Table would be compared with a Full (or Cartesian) Join status, and could be creating the duplicates. It is a possibility that the code marked in RED above might want to be changed to be something like the following:

([Warehouse Info].Alias)=[Calculations].[Alias]
​

 

[Brianwarnock]

You don't have to have a join, the join is basically a where clause, probably, but I've no proof of this, more efficient but it is not mandatory.

Jzwp22 is on the right track

Brian

 

[Fasopus]

([Warehouse Info].Alias)=[Calculations].[Alias]
​

Yeah that is what it should read.

Im getting the just of what you guys are saying. The name the person goes by in the system is the alias. Im not entirely sure were all on the exact same page. Basically what I need is a query that generates five fields. I need Hours from Warehouse Info Table, Alias from Warehouse Info/Performance, Most Recent Date from Performance, Score from Performance and Hours Logged from Performance. From here it should contain entirely unique alias' for the latest date and then all the other information should just be the matching data from the other tables. Right now the code I posted gives me duplicates of the alias' and information from multiple dates

 

[Brianwarnock]

Read and apply Jzwp22's post, Max gives you Max across the group which in your code is 4 fields hence the duplicates.
Find the Max per person then go back into the data and get the rest of the info. This is standard practice.

Brian

 

[Fasopus]

EDIT: Aha! I figured it out, just needed to edit one relationship to make it work perfectly

 

[Brianwarnock]

You have to join on both of these fields
[Most Recent Performances Per Person].ID
[Most Recent Performances Per Person].[Most Recent Date] to the ID and Date (which it has already been pointed out is a bad field name) in the performance table.

Jz has also indicated i think that you may have to extract from the performace table and in a 3rd query extract from the wharehouse if the joins don't permit a 3 way link up.

Brian

 

[Fasopus]

Well looks like I got it all figured out, thanks for the help guys!

 

[Brianwarnock]

EDIT: Aha! I figured it out, just needed to edit one relationship to make it work perfectly

Pity you deleted the original kinda makes my post look stupid.

Brian