Question on Query

[navin]

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

 

[Jerry Whittle]

You will need a crosstab query. You'll also need to define the column
headings with the months or a report won't work correctly if a month is
missing.

 

[KARL DEWEY]

You need two queries. I called the table name 'navin.'
Totals query named navin_1 ---
SELECT navin.[Supplier#], navin.SupplierName, Sum(navin.[On time]) AS
[SumOfOn time], Sum(navin.Rejection) AS SumOfRejection, Count(navin.Period)
AS CountOfPeriod
FROM navin
GROUP BY navin.[Supplier#], navin.SupplierName;

TRANSFORM First([navin].[On time] & Chr(13) & Chr(10) & [navin].[Rejection])
AS Expr1
SELECT navin.SupplierName, [navin_1].[SumOfOn
time]/[navin_1].[CountOfPeriod] & Chr(13) & Chr(10) & "Rejection" AS [Average
on time]
FROM navin INNER JOIN navin_1 ON navin.[Supplier#] = navin_1.[Supplier#]
GROUP BY navin.SupplierName, [navin_1].[SumOfOn
time]/[navin_1].[CountOfPeriod] & Chr(13) & Chr(10) & "Rejection"
PIVOT navin.Period In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");