Query of multiple table lines to one line averaged

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
 
N

Nikos Yannacopoulos

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
 
Y

Yellowbeard

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
 
Y

Yellowbeard

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
 
N

Nikos Yannacopoulos

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
 
N

Nikos Yannacopoulos

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
 
Y

Yellowbeard

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
 
Y

Yellowbeard

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
 
N

Nikos Yannacopoulos

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

How can I set a query to give me a count of items averaged? 4
display 1 line of multiple worksheets into multiple lines on 1 wks 1
Summing multiple input records into one output rcd query 2
Adding multiple fields to a Pivot table at one time 1
Exporting a multiple table Acess file to one Excel file 1
Macro copy of variable ranges in one sheet to table in another she 1
Using a single cell refernce to change multiple formulas 5
Looking for code to separate one line of text into multiple lines in Excel 1

Top