Criteria problems in query (1 Viewer)

H

HorstSnap

New member

Local time

Today, 13:27

Joined

Jan 21, 2009

Messages

8

• Jan 21, 2009

• #1

Hi,
I am trying to build a query where I need to count the games played by a group away and not at home. the fields away and home are drop down menus with either Y or N. First I opened the query wizard and I put the field groupId and away field in the wizard. But then if I want to count the amount of Ys in the Away table I always get the wrong number. I use a count function without a criteria. (putting in "Y" did not help)

is there any solution for this problem?

Thanks in advance,
Horst

 

A

Atomic Shrimp

Humanoid lifeform

Local time

Today, 21:27

Joined

Jun 16, 2000

Messages

1,954

• Jan 21, 2009

• #2

If it's a drop-down field, the bound column may not be the displayed column - so it might show 'Y' or 'N' or whatever, but it might be storing the value of the primary index of the table where the list of drop-down options are coming from.

Take a look at the drop-down properties of the field and find out which column in the source table it's bound to.

 

H

HorstSnap

New member

Local time

Today, 13:27

Joined

Jan 21, 2009

Messages

8

• Jan 22, 2009

• #3

Well, I looked and the bound column for away field is 1. I saw that the bound column for the home field is 1 too. Is this wrong? Because doing the count function the query shows me always double the amount it should. instead of 19 matches away it shows 38.
could this be the problem?
sorry for my english...

 

H

HorstSnap

New member

Local time

Today, 13:27

Joined

Jan 21, 2009

Messages

8

• Jan 22, 2009

• #4

I forgot to say that I tipped in the values of the drop down menus myself, since there is only Y and N.

 

A

Atomic Shrimp

Humanoid lifeform

Local time

Today, 21:27

Joined

Jun 16, 2000

Messages

1,954

• Jan 22, 2009

• #5

It sounds like you might somehow be running a cartesian query - that is, a query where you have multiple tables, not joined to each other at all - the number of rows returned is usually the multiplied product of the number of rows in the two tables.

Given that you've presumably got a table with just two rows ('Y' and 'N'), could it be that you've dropped this table into your query, without joining it to anything?

 

H

HorstSnap

New member

Local time

Today, 13:27

Joined

Jan 21, 2009

Messages

8

• Jan 22, 2009

• #6

This could be, since I dont know how to join it to something. In the table I have also a column with eventids, always two times the same one under the other, since I have to put for each of the two groups if it plays at home or away. next to the eventid I have put a groupid column, and put for every event the two groups in. the last to rows are the home and away columns, each with the drop down menu. Do I have to join the lookup wizard to something? perhaps the groupid?

 

H

HorstSnap

New member

Local time

Today, 13:27

Joined

Jan 21, 2009

Messages

8

• Jan 22, 2009

• #7

again I forgot to say that in the first column is an ID created by access. (The primary key).

 

H

HorstSnap

New member

Local time

Today, 13:27

Joined

Jan 21, 2009

Messages

8

• Jan 22, 2009

• #8

Could it be that this problem has to do with the settings of the lookup wizard? But if I don't use the lookup wizard and just leave the fields as text fields , should it then work? When I use the count function, what should I insert in criteria ?
Thx