Query of multiple table lines to one line averaged

  • Thread starter Thread starter Yellowbeard
  • Start date Start date
Y

Yellowbeard

Hi,
I am new to this. I have a table:

AREA - BADGE - FIRST NAME - LAST NAME - BLDG# - BLDG NAME - DATE - SCORE

Each line is for each person in an area with a score. Each area has multiple entries.
I want a summary that takes each month and gives me an average score on one line for each area.

AREA - JAN - FEB - MARCH - APRIL
100 - 2.54 - 2.12 - 1.54 - 2.22
102 - 1.87 - 1.89 - etc - etc
103 - etc
etc

I have other queries utilizing all the info, but i can not figure out how to summarize and average the score.

Any input is much appreciated.
Martin
 
Martin,

Open a new query in design view and select your table.
In the table box double-click on the following fields, to get them in the grid:
AREA
SCORE

Now go to the next available field in the grid, and in the Field line type in:
Month_: Format$(Month([DATE]),"mmmm")

Next, go to the Query menu and select Crosstab Query. You will notice a couple of new lines in the grid. Set the following:
AREA: Total: Group By, Crosstab: Row Heading
SCORE: Total: Avg, Crosstab: Value
Month_: Total: Group By, Crosstab: Column Heading

With a bit of luck, you're there.... without luck is if your DATE field is in text rather than date format, in which case the expression must change according to the actual format of the data.

HTH,
Nikos

Hi,
I am new to this. I have a table:

AREA - BADGE - FIRST NAME - LAST NAME - BLDG# - BLDG NAME - DATE - SCORE

Each line is for each person in an area with a score. Each area has multiple entries.
I want a summary that takes each month and gives me an average score on one line for each area.

AREA - JAN - FEB - MARCH - APRIL
100 - 2.54 - 2.12 - 1.54 - 2.22
102 - 1.87 - 1.89 - etc - etc
103 - etc
etc

I have other queries utilizing all the info, but i can not figure out how to summarize and average the score.

Any input is much appreciated.
Martin
 
Nikos,
All seems to go well except I get an error of:

Undefined function 'Format$' in expression.

I do have the date field in date/time format. all dates show mm/dd/yyyy.

Any changes? Any ideas?
Martin
Martin,

Open a new query in design view and select your table.
In the table box double-click on the following fields, to get them in the grid:
AREA
SCORE

Now go to the next available field in the grid, and in the Field line type in:
Month_: Format$(Month([DATE]),"mmmm")

Next, go to the Query menu and select Crosstab Query. You will notice a couple of new lines in the grid. Set the following:
AREA: Total: Group By, Crosstab: Row Heading
SCORE: Total: Avg, Crosstab: Value
Month_: Total: Group By, Crosstab: Column Heading

With a bit of luck, you're there.... without luck is if your DATE field is in text rather than date format, in which case the expression must change according to the actual format of the data.

HTH,
Nikos

Hi,
I am new to this. I have a table:

AREA - BADGE - FIRST NAME - LAST NAME - BLDG# - BLDG NAME - DATE - SCORE

Each line is for each person in an area with a score. Each area has multiple entries.
I want a summary that takes each month and gives me an average score on one line for each area.

AREA - JAN - FEB - MARCH - APRIL
100 - 2.54 - 2.12 - 1.54 - 2.22
102 - 1.87 - 1.89 - etc - etc
103 - etc
etc

I have other queries utilizing all the info, but i can not figure out how to summarize and average the score.

Any input is much appreciated.
Martin
 
When I save it puts the columns in design view in different order???
AREA
Month_
SCORE

Does this matter?
Martin
Martin,

Open a new query in design view and select your table.
In the table box double-click on the following fields, to get them in the grid:
AREA
SCORE

Now go to the next available field in the grid, and in the Field line type in:
Month_: Format$(Month([DATE]),"mmmm")

Next, go to the Query menu and select Crosstab Query. You will notice a couple of new lines in the grid. Set the following:
AREA: Total: Group By, Crosstab: Row Heading
SCORE: Total: Avg, Crosstab: Value
Month_: Total: Group By, Crosstab: Column Heading

With a bit of luck, you're there.... without luck is if your DATE field is in text rather than date format, in which case the expression must change according to the actual format of the data.

HTH,
Nikos

Hi,
I am new to this. I have a table:

AREA - BADGE - FIRST NAME - LAST NAME - BLDG# - BLDG NAME - DATE - SCORE

Each line is for each person in an area with a score. Each area has multiple entries.
I want a summary that takes each month and gives me an average score on one line for each area.

AREA - JAN - FEB - MARCH - APRIL
100 - 2.54 - 2.12 - 1.54 - 2.22
102 - 1.87 - 1.89 - etc - etc
103 - etc
etc

I have other queries utilizing all the info, but i can not figure out how to summarize and average the score.

Any input is much appreciated.
Martin
 
No, it doesn't. It's just putting columns in the order it prefers: Row headings, Column heading, Value.

Nikos
When I save it puts the columns in design view in different order???
AREA
Month_
SCORE

Does this matter?
Martin
Martin,

Open a new query in design view and select your table.
In the table box double-click on the following fields, to get them in the grid:
AREA
SCORE

Now go to the next available field in the grid, and in the Field line type in:
Month_: Format$(Month([DATE]),"mmmm")

Next, go to the Query menu and select Crosstab Query. You will notice a couple of new lines in the grid. Set the following:
AREA: Total: Group By, Crosstab: Row Heading
SCORE: Total: Avg, Crosstab: Value
Month_: Total: Group By, Crosstab: Column Heading

With a bit of luck, you're there.... without luck is if your DATE field is in text rather than date format, in which case the expression must change according to the actual format of the data.

HTH,
Nikos

Hi,
I am new to this. I have a table:

AREA - BADGE - FIRST NAME - LAST NAME - BLDG# - BLDG NAME - DATE - SCORE

Each line is for each person in an area with a score. Each area has multiple entries.
I want a summary that takes each month and gives me an average score on one line for each area.

AREA - JAN - FEB - MARCH - APRIL
100 - 2.54 - 2.12 - 1.54 - 2.22
102 - 1.87 - 1.89 - etc - etc
103 - etc
etc

I have other queries utilizing all the info, but i can not figure out how to summarize and average the score.

Any input is much appreciated.
Martin
 
Martin,

It's funny you should get this... what version of Access do you have? Try skipping the dollar sign.

In the meantime: I found out that Format() and Month() don't work well together for some reason! So, you should use one or the other in the expression, whichever works for you. Alternatives:

=Month([Date])
=Format([Date],"mmm")

The former will return numbers 1 -12. The latter will return full month names, but the column sequence is alphabetical on month names - not good. A middle of the road solution is =Format([Date],"mm - mmm") which returns 01 - Jan, 02 - Feb etc.

HTH,
Nikos


Nikos,
All seems to go well except I get an error of:

Undefined function 'Format$' in expression.

I do have the date field in date/time format. all dates show mm/dd/yyyy.

Any changes? Any ideas?
Martin
Martin,

Open a new query in design view and select your table.
In the table box double-click on the following fields, to get them in the grid:
AREA
SCORE

Now go to the next available field in the grid, and in the Field line type in:
Month_: Format$(Month([DATE]),"mmmm")

Next, go to the Query menu and select Crosstab Query. You will notice a couple of new lines in the grid. Set the following:
AREA: Total: Group By, Crosstab: Row Heading
SCORE: Total: Avg, Crosstab: Value
Month_: Total: Group By, Crosstab: Column Heading

With a bit of luck, you're there.... without luck is if your DATE field is in text rather than date format, in which case the expression must change according to the actual format of the data.

HTH,
Nikos

Hi,
I am new to this. I have a table:

AREA - BADGE - FIRST NAME - LAST NAME - BLDG# - BLDG NAME - DATE - SCORE

Each line is for each person in an area with a score. Each area has multiple entries.
I want a summary that takes each month and gives me an average score on one line for each area.

AREA - JAN - FEB - MARCH - APRIL
100 - 2.54 - 2.12 - 1.54 - 2.22
102 - 1.87 - 1.89 - etc - etc
103 - etc
etc

I have other queries utilizing all the info, but i can not figure out how to summarize and average the score.

Any input is much appreciated.
Martin
 
Thank you for your responce.
I have Office 2000 with SP1 and other updates.
I will try these tips.
Thank you,
Martin
Martin,

It's funny you should get this... what version of Access do you have? Try skipping the dollar sign.

In the meantime: I found out that Format() and Month() don't work well together for some reason! So, you should use one or the other in the expression, whichever works for you. Alternatives:

=Month([Date])
=Format([Date],"mmm")

The former will return numbers 1 -12. The latter will return full month names, but the column sequence is alphabetical on month names - not good. A middle of the road solution is =Format([Date],"mm - mmm") which returns 01 - Jan, 02 - Feb etc.

HTH,
Nikos


Nikos,
All seems to go well except I get an error of:

Undefined function 'Format$' in expression.

I do have the date field in date/time format. all dates show mm/dd/yyyy.

Any changes? Any ideas?
Martin
Martin,

Open a new query in design view and select your table.
In the table box double-click on the following fields, to get them in the grid:
AREA
SCORE

Now go to the next available field in the grid, and in the Field line type in:
Month_: Format$(Month([DATE]),"mmmm")

Next, go to the Query menu and select Crosstab Query. You will notice a couple of new lines in the grid. Set the following:
AREA: Total: Group By, Crosstab: Row Heading
SCORE: Total: Avg, Crosstab: Value
Month_: Total: Group By, Crosstab: Column Heading

With a bit of luck, you're there.... without luck is if your DATE field is in text rather than date format, in which case the expression must change according to the actual format of the data.

HTH,
Nikos

Hi,
I am new to this. I have a table:

AREA - BADGE - FIRST NAME - LAST NAME - BLDG# - BLDG NAME - DATE - SCORE

Each line is for each person in an area with a score. Each area has multiple entries.
I want a summary that takes each month and gives me an average score on one line for each area.

AREA - JAN - FEB - MARCH - APRIL
100 - 2.54 - 2.12 - 1.54 - 2.22
102 - 1.87 - 1.89 - etc - etc
103 - etc
etc

I have other queries utilizing all the info, but i can not figure out how to summarize and average the score.

Any input is much appreciated.
Martin
 
Nikos,
If you are there.... I have tried all your variations and they do not work.
I am stumped. It is on the right path but I can not figure it out.
Any further suggestions????
Martin
Hi,
I am new to this. I have a table:

AREA - BADGE - FIRST NAME - LAST NAME - BLDG# - BLDG NAME - DATE - SCORE

Each line is for each person in an area with a score. Each area has multiple entries.
I want a summary that takes each month and gives me an average score on one line for each area.

AREA - JAN - FEB - MARCH - APRIL
100 - 2.54 - 2.12 - 1.54 - 2.22
102 - 1.87 - 1.89 - etc - etc
103 - etc
etc

I have other queries utilizing all the info, but i can not figure out how to summarize and average the score.

Any input is much appreciated.
Martin
 
Martin,

I cannot think what might be the problem... you're welcome to zip your ..mdb and mail it, if you want me to have a look at it.

Nikos

Nikos,
If you are there.... I have tried all your variations and they do not work.
I am stumped. It is on the right path but I can not figure it out.
Any further suggestions????
Martin
Hi,
I am new to this. I have a table:

AREA - BADGE - FIRST NAME - LAST NAME - BLDG# - BLDG NAME - DATE - SCORE

Each line is for each person in an area with a score. Each area has multiple entries.
I want a summary that takes each month and gives me an average score on one line for each area.

AREA - JAN - FEB - MARCH - APRIL
100 - 2.54 - 2.12 - 1.54 - 2.22
102 - 1.87 - 1.89 - etc - etc
103 - etc
etc

I have other queries utilizing all the info, but i can not figure out how to summarize and average the score.

Any input is much appreciated.
Martin
 
Back
Top