UPDATE field in table based on DISTINCT values

[sduran]

Below is a sample of the table with the data. I manually added the 1 and 0 to the hc_Year field. However, I would like to create an Update query that will add a 1 to the hc_Year if its the first instance of PIDM & regsYear and add a 0 to the records that are not the first instance.

Please help. I've been searching on online but I've come up with nothing. Sadly, I give in. Please help.

PIDM | regsYear | hc_Year
52 | 2009 | 1
52 | 2010 | 1
201 | 2007 | 1
201 | 2007 | 0
201 | 2007 | 0
201 | 2008 | 1

 

[Pat Hartman]

You will need to create a VBA loop to do this. Don't forget to sort your query. All the query roads seem to lead to aggregate queries which are not updateable. Storing the "first" record in a temp table is a possibility but the problem seems to revolve around attempting to assign a fixed order to rows which is not supported by relatioal databases so there is no tool to make it easy to do. Relational tables are ordered by sorting and you can only order consistantly if the column you are sorting on is unique.

 

[sduran]

Thank you Pat,

I spent all day Monday and Tuesday trying to figure this out. I thought it was just me...that I could not figure it out using an UPDATE query. I wanted to make the update to the table without having to write code.

Updating the field on this table is the last step in a process that I'm working on in Access. However, I had to export the table to Excel to update the field using a formula =IF(AE2=AE1,0,1), which is not problem because I had to create charts based on the data anyway. But I wanted to automate the process as much as possible for users.

This is my first time posting on a Forum. It felt great to have help!

Thank you again Pat.

 

[Pat Hartman]

What you are doing violates normal forms and that is why there is no query that can help you. It is wrong by relational database standards.

If you can explain the purpose of this update, I might be able to offer a solution you can implement with queries.

 

[sduran]

Yes, thank you.

Okay, so I'm preparing data for reporting and graphing by Year.
There are more fields to this table that make the record unique. For example each record constitutes a class a student took with credit hours. So, I can sum the credit hours per year the way the data is, no problem. However, I need to be able to get a head count (un-duplicated) per year. I was doing this in Excel with the formula i posted before, but want to automate data gathering process in Access. So that all I have to do is export the data to Excel and pivot/chart the data.

I was hoping there was a way to add the 1 and 0 so that when I pivot and can sum the hc_year field and which will give me the head count.

I am open to suggestions. Thank you again.

PIDM | regsYear | hc_Year | Class | Crd Hrs
52 | 2009 | 1 | 7895 | 3
52 | 2010 | 1 | 7854 | 3
201 | 2007 | 1 | 3652 | 3
201 | 2007 | 0 | 2585 | 3
201 | 2007 | 0 | 8458 | 3
201 | 2008 | 1 | 4584 | 3

 

[spikepl]

If you make a query, where you order by PIDM, regsYear then the (untested) function below inserted in that same query should give you your 1's and 0':

Public Function Hcyear(PIDM as Long, regsYear as Integer) As Long
Static PIDMPrevious as Long
Static regsYearPrevious as Integer
if PIDM<>PIDMPrevious OR regsYear<>regsYearPrevious then
   hcyear=1
else
   hcyear=0
end if
End Function

 

[sduran]

Thank you spikepl.

Where do I setup the values for PIDMPrevious and regsYearPrevious? And will this UPDATE the field in the table instead of creating a separate query? If so, what would that look like?

Thank you.