dcount?

[malform]

I have only been using access for about 3 weeks now, and its kicking my butt pretty hard.

Im making a query that does all kinds of math junk in it. I want to be able to find the number of occurrences of x in another column in the table.

For a better example, lets say I have a column named "SP" in the "compiled" table, the values of this column range from 1 to 5, in about 200 entries. So there is another column in the compiled table called "SPX", which has the same value range. So if I am looking at the one entry in the table, I want to take its value in the "SPX" column, and see how many times it shows up in the "SP" column.

I have been trying to get dcount to do this, but I cant seem to get it to work... Must have tried a dozen ways now.

Any help would be awesome, thank you.

 

[Karma]

Is this what you mean?

Look at QRY_SP and QRY_SPX

QRY_SPX uses values selected in QRY_SP.

HTH

K.

 

[malform]

Thank you for responding!

But, Im not sure... I cant open your file, I have access 2000 here at work, this is probably the problem.. You you save it down for me please?

 

[JonyBravo]

Hi!

Can you explain better what do you want?

JonyBravo

 

[Karma]

Sorry try this.

 

[malform]

I think I see what you are doing there. Linking the 2 columns together. I dont think that will work out with what I am trying to do.

Ok, I trimmed down my database so I may share it. I was trying to make my question more simple so I wouldnt have to go through a bunch of explaining, but Im thinking that was the wrong thing to do.

If you look at the MASS test query you will see a ton of information. The last 4 columns are the ones I am working on. The Expr1 column is the start of the math, once I get the looking up/count thing figured out it will be added to it. The Expr2-Expr4 columns are all tests.

In these test columns I am trying to find how many times the value in the "SP Effect" column for this on record comes up in the entire "Faction" column.

I hope this is better at conveying my question properly. Thank you so much for the quick replies, this is great!

 

[malform]

Nobody has any ideas on how to do this?

 

[Karma]

Hi,

I'm still not 100% certain what you mean however this may give you some ideas.

It is a new query built on MASS test. It uses a second select query inside the first that counts the number of SP.Effect entries that match the number of Faction entries for every row.

Paste this into the sql view of the query builder.

SELECT [MASS test].*, (SELECT Count(compiled2.[SP Effect]) AS [CountOfSP Effect] FROM compiled2 WHERE (((compiled2.Faction)=[MASS Test].[Faction]));) AS Expr5
FROM [MASS test];

HTH.

K.