Using DCount/Dlookup

[Ankarn]

I have a query, where i have a Field A that is the primary key.
Field B is not primary, I can have, lets say 70 records. Field B can contain only 2-3 different values.

What i want to do here is to make a report, where the lines is not every record, but every different value of B and the number of records with that value in field B.

So if there is 3 different values in B, 1 2 and 3. And there is 20 of type 1, 33 of type 2 and 17 of type 3, i want the records in the report to be the values 1 2 and 3 with the number of each of them. Like

1 20
2 33
3 17

I really dont know how to do this, i dont know how to get the different values. When i have the values i could just make a DCount with criteria regarding the type.

How could i make this report like i want, with only these three(in this case) records?

I would really apreciate help on this one.

 

[namliam]

Make a GROUP BY query...

Make a new query and find the "Sigma" sign on the tool bar...
Now add field B twice to your design grid. Leave one of them as "Group by" set the other to "Count"
Run the query.

 

[Ankarn]

Ah.. smart.

Every value in Field B all has the same value in Field C and D.

What do i do if i would like to show the Field-B's value in C and D?

 

[namliam]

I dont get your question???

You mean you want to add fields C and D??
Just Add B,C and D, now add B again and change its properties to count.

 

[Ankarn]

wouldnt that be a problem if C and D had different values for one value of B?

I have to use an expression, and i picked first. Since i know they are all alike for each value of B.

 

[namliam]

Could you be shorter in your questions?? Please elaborate a bit more, you are talking shorthand I am gambling as to your real question...

It will not be a problem for the query to have seperate values, except they will be displayed as seperate groups and and counted as such too.

 

[Ankarn]

Field A is the primary
Field B has 3 different values
Field C and D has a standard value for each value of B.

Lets say B has values 1 2 and 3. Field C and D is equal for each B.

But they could not have been.

And i just show one record for each value of B. If C and D had different values for B = 1, then what would be displayd in the first record for field C and D?

In my case C is equal for every B = 1, and then i could just use first.

But i got this working as i want, so it's ok.

 

[namliam]

b c d
1 1 1
1 1 1
1 1 1
1 2 1
2 2 2
2 2 2
2 2 2
2 2 2
2 2 3

If you query above table it will return
1 1 1 3
1 2 1 1
2 2 2 4
2 2 3 1

I hope this answers your question??

 

[Ankarn]

Ah.. ok.

Yes. Then i understand.

But for my purpose, i just as well use first.

 

[namliam]

Good luck!