Count function for single lines of query

[Guest]

Hi,
I use a DB of Purchase Orders, and I created a query about detailed orders
(single lines, products, amounts, ID Order, etc.).
Now I have as many lines (in the query) as the total lines in the orders,
with the ID order field that sometimes is repeated (since for a single order
there may be many lines).
What I need is a new field in the query that counts how many rows there are
in the query with the same ID Order number, for each single line.
for example:
ID Order Product Count Field
1 aaa 3
1 bbb 3
1 ccc 3
2 xxx 1

I tried with the Count function, but it returned me the total count of
records where ID Order is = ID Order in the query, i.e. the total amount of
lines (62).
I should make a kind of function as in Excel, that search in each single
line the ID Order value, and counts how many records with that value are
included in the query.

Thanks in advance.

Fabio CH

 

[Michel Walsh]

Hi,


SELECT COUNT*)
FROM ( SELECT DISTINCT ID FROM myTable)



Hoping it may help,
Vanderghast, Access MVP

 

[John Spencer (MVP)]

Sounds as if you might want to use the DCount function

SELECT [Id Order], [Product],
DCount("*","YourTableName","[Id Order]=" & [Id Order]) as CountThem,
FROM YourTableName

 

[Guest]

Hi Michel,
sorry but I'm a newcomer on Access, and I don't know how to correctly type
the instructions on the query.
Could you help me? cause I wrote it down as it's below, but it gave me errors.

Thanks!!

 

[Guest]

Hi John,
indeed I tried to use the DCount function, and it worked correctly, but
either for the total number of orders, or for one single number of P.Order.
My function was: DCount("[IDOrder]", "Table of Detailed orders",
"[IDOrder]=[IDOrder]").
doing so, I had on every row the same value repeated, i.e. the total number
of orders (62).
If I change the function, and type:
DCount("[IDOrder]", "Table of Detailed orders", "[IDOrder]=1"). it works,
and it gives me the number 3, total # of the lines in the order #1, but doing
so it's working as a filter, with only one value each time.
I would like to edit this function in order to recognise (instead of #1) the
P.O. number for each single line of the query.

Thanks!!

Sounds as if you might want to use the DCount function

SELECT [Id Order], [Product],
DCount("*","YourTableName","[Id Order]=" & [Id Order]) as CountThem,
FROM YourTableName

Hi,
I use a DB of Purchase Orders, and I created a query about detailed orders
(single lines, products, amounts, ID Order, etc.).
Now I have as many lines (in the query) as the total lines in the orders,
with the ID order field that sometimes is repeated (since for a single order
there may be many lines).
What I need is a new field in the query that counts how many rows there are
in the query with the same ID Order number, for each single line.
for example:
ID Order Product Count Field
1 aaa 3
1 bbb 3
1 ccc 3
2 xxx 1

I tried with the Count function, but it returned me the total count of
records where ID Order is = ID Order in the query, i.e. the total amount of
lines (62).
I should make a kind of function as in Excel, that search in each single
line the ID Order value, and counts how many records with that value are
included in the query.

Thanks in advance.

Fabio CH

 

[Michel Walsh]

Hi,


And my message was having an horrible typo.

You cut and paste what is below in a SQL view, but change myTable for your
real table name:


SELECT COUNT(*)
FROM ( SELECT DISTINCT id FROM myTable )

That should work without problem if the table name is well formed (ie, no
need to use [ ] around it because it has a space or an illegal character).
Alternatively, make two queries, one with

SELECT DISTINCT id FROM myTable

save it, say, as Q1. Then, make

SELECT COUNT(*) FROM Q1


Once you have the text fine in the SQL view, you can revert in design view
to see how you could have "graphically" done to get the same result (it is
easier to specify the SQL statement than to describe the whole graphical
sequence, but I admit, the graphical interface is quite interesting to use
too! )



Hoping it may help,
Vanderghast, Access MVP

 

[John Spencer (MVP)]

Sorry, been out of town.

Modify your use of the function so that the function gets the IDOrder for each row.

DCount("[IDOrder]", "[Table of Detailed orders]", "[IDOrder]=" & [IDOrder])

Note that the above has changed the placement of the QUOTE marks in the end
criteria portion.

Hi John,
indeed I tried to use the DCount function, and it worked correctly, but
either for the total number of orders, or for one single number of P.Order.
My function was: DCount("[IDOrder]", "Table of Detailed orders",
"[IDOrder]=[IDOrder]").
doing so, I had on every row the same value repeated, i.e. the total number
of orders (62).
If I change the function, and type:
DCount("[IDOrder]", "Table of Detailed orders", "[IDOrder]=1"). it works,
and it gives me the number 3, total # of the lines in the order #1, but doing
so it's working as a filter, with only one value each time.
I would like to edit this function in order to recognise (instead of #1) the
P.O. number for each single line of the query.

Thanks!!

Sounds as if you might want to use the DCount function

SELECT [Id Order], [Product],
DCount("*","YourTableName","[Id Order]=" & [Id Order]) as CountThem,
FROM YourTableName

Hi,
I use a DB of Purchase Orders, and I created a query about detailed orders
(single lines, products, amounts, ID Order, etc.).
Now I have as many lines (in the query) as the total lines in the orders,
with the ID order field that sometimes is repeated (since for a single order
there may be many lines).
What I need is a new field in the query that counts how many rows there are
in the query with the same ID Order number, for each single line.
for example:
ID Order Product Count Field
1 aaa 3
1 bbb 3
1 ccc 3
2 xxx 1

I tried with the Count function, but it returned me the total count of
records where ID Order is = ID Order in the query, i.e. the total amount of
lines (62).
I should make a kind of function as in Excel, that search in each single
line the ID Order value, and counts how many records with that value are
included in the query.

Thanks in advance.

Fabio CH