You don't need the New Field field to be in the table. The "more than one"
record being returned likely is because of how I used the Count function.
I've set up a test table with two fields in it: PrimaryKeyFieldName and
Invoice #. The following query will return the invoice number and the letter
sequence (it's ordered by the invoice number but not by the letter):
SELECT TableName.[Invoice #], Chr(64+(SELECT Count(*) AS Alph
FROM TableName AS T
WHERE T.[Invoice #] = TableName.[Invoice #]
AND T.PrimaryKeyFieldName <=
TableNameForTest.PrimaryKeyFieldName )) AS [New Field]
FROM TableName
ORDER BY TableNameForTest.[Invoice #];
If you want to order the letter sequence along with the invoice number, then
you'll need a second query (assume it's named qryInvoiceAndLetter) that uses
the above query as its table source:
SELECT Q.[Invoice #], Q.[New Field]
FROM qryInvoiceAndLetter AS Q
ORDER BY Q.[Invoice #], Q.[New Field];
--
Ken Snell
<MS ACCESS MVP>
SHIPP said:
The error message I get is:
AT MOST ONE RECORD CAN BE RETURNED BY THIS SUBQUERY.
My table name is: TableName
The fields within the table are:
[PrimaryKeyFieldName] Autonumber
[Invoice #] Text
[New Field] Text
The data in the file is:
PrimaryKeyFieldName Invoice #
1 3
2 1
3 2
4 2
5 1
6 3
7 3
8 1
Ken Snell said:
What error message?
You will get only an A from the use of your change because the subquery will
only find one match; the query that I posted is finding how many records are
at or below that primary key's value.
Can you post details about your table structure so that I can better
understand what's happening?
--
Ken Snell
<MS ACCESS MVP>
I received an error message(only one record selected) when I utilized...
T.PrimaryKeyFieldName <= TableName.PrimaryKeyFieldName
When I changed the above line to...
T.PrimaryKeyFieldName = TableName.PrimaryKeyFieldName
I did not receive any error message but field [Field Name] had an "A" for
each record. It didn't escalate the letter when say invoice number 1
appeared
3 times. The original table had
1
1
1
The new table had
1 A
1 A
1 A
What I wanted was
1 A
1 B
1 C
By the way I'm using Access 97. Any additional help would be greatly
appreciated.
:
We'll get there.... this should get you closer!
SELECT TableName.[Invoice #], Chr(63 +
(SELECT Count(T.[Invoice #]) AS Alph
FROM TableName AS T
WHERE T.[Invoice #] = TableName.[Invoice #]
AND T.PrimaryKeyFieldName <= TableName.PrimaryKeyFieldName
GROUP BY T.[Invoice #], T.PrimaryKeyFieldName
ORDER BY T.PrimaryKeyFieldName)) AS [New Field]
FROM TableName
ORDER BY TableName.[Invoice #], TableName.[New Field];
--
Ken Snell
<MS ACCESS MVP>
Nope, I am still getting the same error message. The file is called
TableName
There are two fields contained in the file:
PrimaryKeyFieldName
Invoice #
Any help would be greatly appreciated.
:
That's what I get for typing the query without testing.... ok, I
think
this
does it:
SELECT TableName.[Invoice #], Chr(63 +
(SELECT Count(T.[Invoice #]) AS Alph
FROM TableName AS T
WHERE T.[Invoice #] = TableName.[Invoice #]
AND T.PrimaryKeyFieldName <= TableName.PrimaryKeyFieldName
GROUP BY T.[Invoice #]
ORDER BY T.PrimaryKeyFieldName)) AS [New Field]
FROM TableName
ORDER BY TableName.[Invoice #], TableName.[New Field];
--
Ken Snell
<MS ACCESS MVP>
Still getting the same error message. T.PrimaryKeyField is not
part of
the
aggregate function. Help!
:
My error -- try this:
SELECT TableName.[Invoice #], Chr(63 +
(SELECT Count(T.[Invoice #]) AS Alph
FROM TableName AS T
WHERE T.[Invoice #] = TableName.[Invoice #]
AND T.PrimaryKeyFieldName <= TableName.PrimaryKeyFieldName
GROUP BY T.[Invoice #]
ORDER BY T.PrimaryKeyFieldName)) AS [New Field]
FROM TableName
ORDER BY TableName.[Invoice #], TableName.[New Field];
--
Ken Snell
<MS ACCESS MVP>
I tried your query and it gives me an error that says t is not
part of
an
aggregate function. Please advise.
:
To do this, you'll need one additional field to be "part" of
the
query's
"test". Do you have a primary key field in the table from
which
the
query is
pulling the data? Assuming that you do, then try this
(generic)
query:
SELECT TableName.[Invoice #],
(SELECT (Chr(63 + Count(*))) AS Alph
FROM TableName AS T
WHERE T.[Invoice #] = TableName.[Invoice #]
AND T.PrimaryKeyFieldName <= TableName.PrimaryKeyFieldName
ORDER BY T.PrimaryKeyFieldName) AS [New Field]
FROM TableName
ORDER BY TableName.[Invoice #], TableName.[New Field];
--
Ken Snell
<MS ACCESS MVP>
I have a table with multiple (duplicate) invoice numbers.
What
I
would
like
to be able to do in a query is to read the table and for
every
duplicate
invoice number assign an alpha character in ascending
sequence.
The
original
table looks like this...
Invoice #
1
1
1
2
2
3
3
3
3
4
The results of the query should look like this...
Invoice # New Field
1 A
1 B
1 C
2 A
2 B
3 A
3 B
3 C
3 D
4 A
Any help would be appreciated.
