Updating distinct rows

[jkwok]

Hi,

Assuming I have a table called 'Combined' with three attributes.

Combined
id - Case_Num - Ticket_Count

I need to make an update query so that for every distinct Case_Num, write a "1" in the Ticket_Count. I've tried a few different things using DISTINCT and DISTINCTROWS but I'm getting nowhere!

This seems very simple, but I can't seem to get it to work. Any help would be greatly appreciated.

J

[jkwok]

I've tried the following:

UPDATE Combined SET Combined.Ticket _Count = "1"
WHERE (SELECT DISTINCT Combined.Case_N um FROM Combined;);

I know this doesn't work, but I'm grasping at straws trying to get something to work. Should I make the SELECT query first, and then make the UPDATE query using that SELECT query somehow?

Thanks again,
Jason

[Stewart Ross]

Hi,

Assuming I have a table called 'Combined' with three attributes.

Combined
id - Case_Num - Ticket_Count

I need to make an update query so that for every distinct Case_Num, write a "1" in the Ticket_Count. I've tried a few different things using DISTINCT and DISTINCTROWS but I'm getting nowhere!

This seems very simple, but I can't seem to get it to work. Any help would be greatly appreciated.

J

Hi. I'm sorry but it's not at all clear what you are trying to achieve here. For each row there appears to be a unique ID field, and a non-unique case number. It would really help if you posted some example rows to show what is in your table.

Whatever you do to group or select case numbers, if grouping by case number is the only criterion you apply an update will not differentiate further and you will end up with ALL of the Ticket_Count entries in your table updated to 1. As that can't be what you are trying to achieve, there must be something else involved. Is it that you want to set the Ticket Count on the first occurrence of each different case number to 1, but leave the others at some other value?

It would be really helpful if you could include in your next post a small sample of what the table contains now and what you want to update it to so we can help you more.

-Stewart

[jkwok]

Hi Stewart,

Sorry if my description was unclear. It's as you said: "set the Ticket Count on the first occurrence of each different case number to 1" and leave the others blank or "0" would even suffice.

So here is a sample of the current table, with ticket_count currently all empty.

Case_Num	Ticket_Count
A3747422	
A3747422	
A3747422	
A3747422	
A3747422	
A3747422	
A3841537	
A3841537	
A3887216	
A3902765	
A3933409	
A3933409	
A3933409	
A3933409	
A3933409

I want the end result to look like this:

Case_Num	Ticket_Count
A3747422	1
A3747422	
A3747422	
A3747422	
A3747422	
A3747422	
A3841537	1
A3841537	
A3887216	1
A3902765	1
A3933409	1
A3933409	
A3933409	
A3933409	
A3933409

I hope that that's a little more clear. Thanks for your help!

Jason

[Stewart Ross]

...I want the end result to look like this:

Case_Num	Ticket_Count
A3747422	1
A3747422	
A3747422	
A3747422	
A3747422	
A3747422	
A3841537	1
A3841537	
A3887216	1
A3902765	1
A3933409	1
A3933409	
A3933409	
A3933409	
A3933409

Hi Jason. This is much clearer, thanks. Your ID field isn't shown in the table - is it a number? If it is, it is then possible to select the lowest ID row for each case number. The SQL for updating would then be:
[code=SQL]UPDATE [yourtable] SET [yourtable].[Ticket_Count] = 1
WHERE ((([yourtable].[ID]) In (SELECT Min([yourtable].[ID]) AS MinID
FROM [yourtable]
GROUP BY [yourtable].[Case_Num];)));[/code]Replace [yourtable] with the real name of your table.

On your test data with added semi-random numeric IDs this results in:

ID Case Number Ticket
 1 A3747422	 1
 3 A3747422	 
 9 A3747422	 
10 A3747422	 
12 A3747422	 
15 A3747422	 
 7 A3841537	 1
 8 A3841537	 
 6 A3887216	 1
 4 A3902765	 1
 2 A3933409	 1
 5 A3933409	 
11 A3933409	 
13 A3933409	 
14 A3933409

Try this out and see how it goes for you.

-Stewart

[jkwok]

Hi Stewart,

Thanks for your quick reply! This looks good and I can see how it works. I ran this on my test database and this worked perfectly. The only problem is that a colleague of mine exports this table to excel every day for his reports and removed the ID attribute from the live database as he said it interfered somehow. (which makes no sense to me)

Long story short, I have no ID attribute to use. I hate to trouble you further, but are there any alternatives with just those two original attributes?

Thanks again,
Jason

[Stewart Ross]

Hi Stewart,

Thanks for your quick reply! This looks good and I can see how it works. I ran this on my test database and this worked perfectly. The only problem is that a colleague of mine exports this table to excel every day for his reports and removed the ID attribute from the live database as he said it interfered somehow. (which makes no sense to me)

Long story short, I have no ID attribute to use. I hate to trouble you further, but are there any alternatives with just those two original attributes?

Thanks again,
Jason

Hi Jason. There are no alternatives that would make any sense - without the ID the table breaks a cardinal rule of database design which is that each row stored must be unique. If rows are not unique there is no meaningful way to distinguish between different entries in the table, and in your case all you have is a list of case numbers with some duplicates for reasons unknown.

I think you should tackle the actual problem - the removal of the ID - in order to move this onwards.

Sorry I can't be of more assistance.

-Stewart

[jkwok]

Hi Stewart,

Thank you so much for all your help. I think you're right about the whole situation. I'll make adjustments and I'm sure my colleague will find a way to work through it.

Thanks again, I really appreciate it!

Jason