Question on Query

[nganesha via AccessMonster.com]

Hi All,

I have to create a database which will be used to create monthly
vendor scorecard. Users have to input vendor performance values in a
form for the month which will be saved in a table.


Below is the table structure:


Supplier#
SupplierName
Period (Jan, Feb etc)
On time (10%, 20 %...)
Rejection (100, 300...)


Once the data is entered, database will open a report, which will
display the output something like:


Period Jan Feb Mar Apr YTD Results
(Avg of current and previous months)


On time 10% 20% 15%
Rejection 100 300 200


Could anybody please suggest how to write a query to achieve the above
or any other suggestions. I have to get this done quickly but unable
to get the query.


Please help...


Thanks,
Navin

 

[John W. Vinson]

Hi All,

I have to create a database which will be used to create monthly
vendor scorecard. Users have to input vendor performance values in a
form for the month which will be saved in a table.


Below is the table structure:


Supplier#
SupplierName
Period (Jan, Feb etc)
On time (10%, 20 %...)
Rejection (100, 300...)

Is Jan January 2009? or 2008, or 2010? Will you create a new database every
year?

I'd really recommend using a Date/Time field containing the first or last day
of the month, rather than a text field for the month name!

Once the data is entered, database will open a report, which will
display the output something like:


Period Jan Feb Mar Apr YTD Results
(Avg of current and previous months)


On time 10% 20% 15%
Rejection 100 300 200


Could anybody please suggest how to write a query to achieve the above
or any other suggestions. I have to get this done quickly but unable
to get the query.

You'll need a Report (probably unbound), with two Subreports, each based on
its own Crosstab query. One crosstab will use the Period field as the Column
Header, the other will use the On Time field. No single query will be able to
include both these disparate views of the data.