Automatically number records by groupings?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

condarko - 11 Oct 2005 21:08 GMT

Hello!

I have been trying to figure this out for literally a week, and I'
going bonkers!

I have a form in which records are entered, and each record needs to b
assigned an ID according to the Salesman, Month, Year and Record Number
So far, I got the form to combine the Salesman, Month and Year in th
ID (Ex: RBJan05) by using DLookUp, but I am missing the Record Number.
The problem is, the record number needs to be the order the record i
entered, but restarts when it is a different Salesman, or Month
Year.

I tried creating a field called AutoNumber and starting off every mont
& year combo in the table with a 1, and then entering the expression i
my form:

=DMax("[AutoNumber]","tblSAMSNos","[tblSAMSNos]![MonthYear]
[MonthYear]")+1

and it will increase the number correctly but then will change all th
other Record #'s to that as well. I EVEN tried creating a table fo
every month for the rest of this year and all of next and using a lon
IIf expression, but that didn't work either. Am I totally overthinkin
this? Whoever can solve this is my HERO

--
condark

Reply to this Message

Michel Walsh - 12 Oct 2005 15:50 GMT

Hi,

I won't make it a field, but a computed expression (in a query).

SELECT a.primarykey, COUNT(*) as rank
FROM myTable As a INNER JOIN myTable As b
ON a.salesman = b.salesman
AND a.dateOfSale >= b.dateOfSale
AND Year(a.dateOfSale) = Year(b.dateOfSale)
AND Month(a.dateOfSale)=Month(b.dateOfSale)
GROUP BY a.primarykey

Once saved, it is a matter to use that query with a join implying the
initial table (myTable), over their primarykey value.

SELECT a.*,
a.SalesManInitials & Format(dateOfSale, "mmmyy" ) & Format( b.rank,
"99")
FROM mytable As a INNER JOIN savedQuery As b
ON a.primaryKey = b.primarykey

Hoping it may help,
Vanderghast, Access MVP

> Hello!
>
[quoted text clipped - 21 lines]
> IIf expression, but that didn't work either. Am I totally overthinking
> this? Whoever can solve this is my HERO!

Reply to this Message

condarko - 12 Oct 2005 16:42 GMT

:eek: Wow, I appreciate your response but am very confused...is thi

expression written in VBA? I'm hoping that's the reason why it look
like a foreign language to me, since I don't know VBA whatsoever. I
there any way to put this in expression form? Also, how to you use
join that "implies the initial table over their primarykey value?"
Please dummify this if possible...thank you!!!

Michel Walsh Wrote:

> Hi,
>
[quoted text clipped - 19 lines]
> Hoping it may help,
> Vanderghast, Access MV

--
condark

Reply to this Message

Michel Walsh - 12 Oct 2005 18:09 GMT

Hi,

Those are SQL statements (to be typed in a query, in design-SQL view).

You have to change the name for table myTable, and the fields: primarykey
(the primary key field of the table), salesman, and dateOfSale.

You can first write the first query, and once done, take a look at the data
it generates. You have to save that query under a name, say you use the name
Query1

You then design a second query, still in SQL view, with:

SELECT a.*,
a.SalesmanInitials & Format(dateOfSale, "mmmyy" ) & Format( b.rank,"99")
FROM mytable As a INNER JOIN Query1 As b
ON a.primaryKey = b.primarykey

where, again, myTable, for the table name, and fields SalesmanInitials and
DateOfSale are to be replaced by the name you really use.

Note that you can change the first query to:

SELECT a.primarykey, COUNT(*) as rank
FROM myTable As a INNER JOIN myTable As b
ON a.salesman = b.salesman
WHERE a.dateOfSale >= b.dateOfSale
AND Year(a.dateOfSale) = Year(b.dateOfSale)
AND Month(a.dateOfSale)=Month(b.dateOfSale)
GROUP BY a.primarykey

and you can then edit it, visually, in the query designer... but it MAY be a
little bit slower. The second query should be available in the graphical
view of the designer, when all the names of tables and fields are right.

Hoping it may help,
Vanderghast, Access MVP

> :eek: Wow, I appreciate your response but am very confused...is this
> expression written in VBA? I'm hoping that's the reason why it looks
[quoted text clipped - 27 lines]
>> Hoping it may help,
>> Vanderghast, Access MVP

Reply to this Message

condarko - 12 Oct 2005 21:19 GMT

Alright, I entered the SQL statements exactly as you wrote them an
replaced your table names with my table names, so that it looks a
follows:

Query 1

SELECT a.ctlAutoNumber, COUNT(*) AS rank
FROM tblSAMSNos AS a INNER JOIN tblSAMSNos AS b O
(a.Initials=b.Initials) AND (a.DateEntered>=b.DateEntered) AN
(Year(a.DateEntered)=Year(b.DateEntered)) AN
(Month(a.DateEntered)=Month(b.DateEntered))
GROUP BY a.cltAutoNumber;

and

Query 2

SELECT a.*, a.Initials & Format([DateEntered],"mmmyy")
Format(b.rank,"99")
FROM tblSAMSNos AS a INNER JOIN Query1 AS b O
a.ctlAutoNumber=b.ctlAutoNumber;

However, when I try to run the query (which I'm assuming is the nex
step?), I get the error message "You tried to execute a query that doe
not include the specified expression 'ctlAutoNumber' as part of a
aggregate function.'

Also, does the query need to be run every time I enter a record, or d
I create a join on the relationships page, or is it all included i
this SQL statement? I've never used SQL. Thank you for all of you
help!!!

--
condark

Reply to this Message