Criteria in Query to avoid duplicate in a particular field

[sgowri7]

Hi there,

I am creating a query in Access where I am retrieving 1000's of records - from 4 to 6 different tables / queries.

Among the various different fields that I am retrieving, I have one field which has unique data.

I would like to know if it is possible to add a criteria such a way that no two records are retrieved with the same value from the unique field.

Thanks in advance.

Gowri Shankar. S

 

[Joe4]

It all really depends on why you are receiving duplicate values (do you have a one-to-many relationship, or a problem with your query?).

Also, if there are multiple records, how do you decide on which one you want to return?

In order for us to help you come up with a solution that works for you, I think we are going to need some details and maybe some examples of your data.

It could be something as simple as changing your query relationships, or perhaps using an Aggregate Query where you just return the "First" record.

 

[sgowri7]

Hi Joe,

Let me explain why there would be duplication in the first hand.

I have data about individual Jobs - which are processed by users throughout the day in one table. I do have the attendance details of the users with their shift in another table. In order to determine which ShiftDate a user has worked, I use the time period to calculate the date, i.e. if a job is ended between XX:XX and XX:XX, i take the date corresponding to the criteria.

All goes well as long as the users remain in their shift. Issue arises when a pariticular user changes his shift from morning or night to the other shift after a week off. (i cannot exclude WOs days entirely - as there are people who works on their WO to earn more).


Reg.Sft Fr.Time To.Time Fr.Dt.Time To.Dt.Time
Morning 0:00:02 00:00:01 08/27/11 14:30:02 08/28/11 14:30:01
Night 13:00:00 12:59:59 08/28/11 03:30:00 08/29/11 03:29:59
Morning 0:00:02 00:00:01 08/27/11 14:30:02 08/28/11 14:30:01

This is how I have the times set to identify the shift date. Now when a user changes shift, obviously the jobs that are uploaded/ended during the overlapping time will go into both the previous as well as the next day - causing the duplication.

Since the shift change can happen morning to night or night to morning, I think selecting the last of the duplicate will work.

I hope I was clear in explaining the scenario and requirement.

Regs,
SGS

 

[Joe4]

I hope I was clear in explaining the scenario and requirement.

It helps a little, but not knowing your table and query structures, I still find it quite confusing (your data posting got all smashed together, so I am not quite clear what it is showing me).

Since the shift change can happen morning to night or night to morning, I think selecting the last of the duplicate will work.

From what you say here, it sounds like an Aggregate Query may be what you need, where you take the "Max" or "Last" value found for a group of "duplicate-type" records. I would recommend checking out Aggregate (Totals) Queries in Access's built-in help files and see if that sounds like it will help you get what you need.

 

[sgowri7]

Thanks Joe,

This worked fine.

I do have one other query. Is there a way in Access where I can include criteria which cross checks the previous records?

i.e. as I have stated I do get jobs with processed time returned. If I have to see whether the difference between the start time of the previous job and the current job is greater than 3 hours or not - is there a way to do that?

 

[Joe4]

I do have one other query. Is there a way in Access where I can include criteria which cross checks the previous records?

i.e. as I have stated I do get jobs with processed time returned. If I have to see whether the difference between the start time of the previous job and the current job is greater than 3 hours or not - is there a way to do that?

This is where Excel and Access differ a bunch. In spreadsheet programs like Excel, this is fairly easy and straightforward. However, if databases, the relative position of any record in relation to any other record really doesn't have any meaning. So it cannot be done nearly as easily as it can in Excel.

One proposed method is to use VBA to loop through a sorted recordset.

Here is a link that discusses some possibilities you may want to check out: http://www.access-programmers.co.uk/forums/showthread.php?t=130382