Counting records

[Layth]

Hi, I'm working on a access database project were I need to use the Microsoft Visual Basic editor.

Here's my problem I have a query with a field called AMPORDERFK this field keeps track of the number of ads that a client has, hypothetically every record could have the same value, they don't however.

What I need to do is count all the records that have the same AMPORDERFK value in this query and then output the count to a new table.

Example:

AMPORDERFK, Name
978, Tom
978, Tom
978, Tom
978, Jerry
583, Jerry
583, Jerry
583, Jerry
583, Tom
999, Tom


New Table:
AMPORDERFK, Tom's Count, Jerry's Count
978, 3, 1
583, 1, 3
999, 1, 0


Any help with programming this in a macros would be a huge help as I'm only a little familiar with SQL and visual basic

Thanks in advance,

Layth

 

[hwkranger]

1) visit rules of normalization
2) view rules of any convensional naming schema


aside from that:

Use a CROSS TAB QUERY. Use the wizard, it'll talk you through what you want.

Randall Vollen
National City Bank Corp.

 

[Layth]

hwkranger thanks for your advice.

Let me update as I see that I have an error in my original post, the field AMPORDERFK doesn't record the number of ads a client has, it records the ad's type number, 978 doesn't mean that a client has 978 ads, it means that they have an ad of type 978, and in my example Tom has 3 ads of 978 type and Jerry has 1.

I have reviewed everything that you have posted and fail to realize how it relates to my problem if it was the original problem or not, could you be more specific? What I need to know is how to count the 3 ads of 978 type and insert into a new query, In other words How do I obtain the "New Table" in my example.

Thanks for all of your help and anyone else on this matter,

Layth

 

[petrosky]

Hi,

Randall is right on the money.
Use the crosstab query wizard to get what you need.

From your example the SQL of the query should go a little like this.

TRANSFORM Count(Table1.Test) AS [The Value]
SELECT Table1.AMPORDERFK
FROM Table1
GROUP BY Table1.AMPORDERFK
PIVOT Table1.Name;

Regards,

Peter

Remember- It's nice to be important,
but it's important to be nice

 

[Layth]

Many thanks to both hwkranger and Petrosky this give me the exact query that I'm looking for, in the case with hwkranger your solution was the one I was looking for, but I had a case of me confusing myself.

Thanks again to both!

Layth