cross-table query help

[mitch97144]

Please help

I have a cross-table query wherby it displays each player and the corresponding dates they have played a match. The Played Date field is used as the column header. Where a record is found for a particular player having played on a specific date it enters a 1 beneath the column header

eg 21/11/04
fred smith 1

Is there any way that i can get the '1' to be displayed as a 'P'

example provided of teh report which is generated from the query.

 

[llkhoutx]

Use a conditional if to test for a 1, i.e. iif([Field]=1,"P",[Filed])

 

[mitch97144]

cross-tab query

I have tried setting this within the query and against the field on teh report and it does not work.

Please advise

 

[llkhoutx]

I understand what you're trying to do. Your zip file didn'y add anything.

My earlier posting

iif([Field]=1,"P",[Filed])

should have been

Column:iif([Field]=1,"P",[Field])

where Field is a recordset field, that is, a field from a table in your cross tab query. Column is the new query column name.

It's to give an exact example without your crosstab query and the underlying table(s), and queries, if any.

You might building a query substituting the P and then use that query in your crosstab query. You won't notice any difference in speed.

See the attached zipped A2K mdb.

 

[mitch97144]

query issue

I have used the following command in the query

IIf([Date Played]=[ddmmyy],"P",[Date Played])

I understand that where i have used [ddmmyy] it asks me to key in a date .Once keyed in it is displaying the record ,with the entered date, as a 'P'

What i would like to do is where a record contains a date , not blank , then display as P.
I tried using IIf(IsNotNull([Date Played],"P",[Date Played])
Using this command it does not allow me to run the query. Error appears " The expression you entered is missing a closing parenthsis".

Once i have done this then i can create the cross-table query.

thanks for your help

 

[mitch97144]

query issue

I have now managed to display a P against all records using the following criteria

A: IIf([Date Played]=True,"P",[Date Played])

How do i remove the blank "Date Played" records from the query. I do not want these displayed.

 

[llkhoutx]

Put "Is Not Null" (without the quotation marks) in the query criteria line for the instant column.

 

[mitch97144]

query issue

Thanks the zero dateplayed records no longer appear.

An example of the query when run.

ClubName RecordID RegID Salutation GameType Date Played A
-----------------------------------------------------------------------
mufc 4 1256 Fred Smith Divisional Trophy 08/12/2004 P
mufc 5 1256 Fred Smith League Cup 15/12/2004 P
swfc 6 2548 Joe Bloggs Divisional Trophy 08/12/2004 P
swfc 7 2548 Joe Bloggs Divisional Trophy 22/12/2004 P

OK. I need to create a cross-table query so that i will have a single record in the query for Fred Smith but with column headings shown as the played date and being diaplayed as P.

eg
club name regid Salutation 08/12/2004 15/12/04 22/12/04
mufc 1256 Fred SMith P P
swfc 2548 Joe Bloggs P P

is this possible????

 

[mitch97144]

query issue

I have now managed to create a cross-table query that will display correctly , per date , when a player has played a match.

Thanks for your information :