Updating a Grouping Field - TransNumber

[Casperov]

Hello,

I am attempting to update a TransNumber field in a table. The TransNumber field will be a grouping number, which will be based on a GLDate field. Therefore, this number will be the same for all the transactions unless the GLDate field changes. At which point I would need to increment the TransNumber field by 1 or more.

I am not sure if using queries or VBA would be best. I am new to vba, and don’t really know where to start.

Thanks,
Casperov

 

[Casperov]

Not quite sure I understand.

Hi Pat,

Let me clarify...I wasn't really clear. The TransNumber will be the same for all of the transactions of the same GLDate. As soon as the GLDate changes, I need the TransNumber to increment. Therefore, if there are thirty transactions, ten from each day, then there will be only three different TransNumbers.

Are you saying that I should sequence the transactions and the get the max GLDate? At which point should I be checking the sequence number of the transactions and then applying a TransNumber that way?

Thanks,
Casperov

 

[Casperov]

Okay,

Make two tables from my tblTransactions table? Does 1 to many mean that each day can have many JournalNumbers(instead of TransNumber)? (I'm switching my terminology - I clarified it with the user)

I have created a table called tblUniqueDates, which grabs all the unique dates from my table of transactions. Now, I'm trying to add another column with an autonumber field. Is there a way to do it with a query?

Thanks Pat,
Mike