Crosstab problem

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi!

I have a problem that I can't solve myself, but I know it can be solved in
Access. Can someone help me?

I should get a report like this:

Company1 Company 2 Company 3
Week 40
Given
sum(given)
Payed
sum(payed)
Given-payed
sum(given-payed)

Week 41
same things

Week 42
and so one

every company sum(given-payed)

Given and payed are calculated in a query. I have tried to make a report
where there're several columns, but i've met some problems. If for example
company 1 haven't got any details in week 41, there should be empty space so
that the details of certain week are in the same row. And I don't know how to
calculate the sum of rows. The row headings should come only once to the left
..

I tried to export the report to Excel but it didn't maintain the structure.
So, what can be done?

Tellu
 
Here's my SQL. Iis it too difficult in finnish?

SELECT Autoilijat.[Yrityksen nimi], Tilitystiedot.Viikko,
Tilitystiedot.Tuotenro, Tilitystiedot.Määrä, Tilitystiedot.Hinta,
Tilitystiedot.Syöttöhinta, Asiakashinnat.Hinta,
Sum([määrä]*nz([tilitystiedot].[Hinta],0)+[määrä]*nz([tilitystiedot].[syöttöhinta],0)) AS Tilitetty, Sum([määrä]*[asiakashinnat].[hinta]) AS Laskutettu
FROM ((Tilitykset INNER JOIN Autoilijat ON Tilitykset.Autoilijanro =
Autoilijat.Autoilijanro) INNER JOIN Tilitystiedot ON Tilitykset.Tilitysnro =
Tilitystiedot.Tilitysnro) INNER JOIN Asiakashinnat ON Tilitystiedot.Tuotenro
= Asiakashinnat.Tuotenro
GROUP BY Autoilijat.[Yrityksen nimi], Tilitystiedot.Viikko,
Tilitystiedot.Tuotenro, Tilitystiedot.Määrä, Tilitystiedot.Hinta,
Tilitystiedot.Syöttöhinta, Asiakashinnat.Hinta;
 
Being as how I do not speak Alute, I can not translate what you said you want
for a crosstab query from your select query. More please.

Tellu said:
Here's my SQL. Iis it too difficult in finnish?

SELECT Autoilijat.[Yrityksen nimi], Tilitystiedot.Viikko,
Tilitystiedot.Tuotenro, Tilitystiedot.Määrä, Tilitystiedot.Hinta,
Tilitystiedot.Syöttöhinta, Asiakashinnat.Hinta,
Sum([määrä]*nz([tilitystiedot].[Hinta],0)+[määrä]*nz([tilitystiedot].[syöttöhinta],0)) AS Tilitetty, Sum([määrä]*[asiakashinnat].[hinta]) AS Laskutettu
FROM ((Tilitykset INNER JOIN Autoilijat ON Tilitykset.Autoilijanro =
Autoilijat.Autoilijanro) INNER JOIN Tilitystiedot ON Tilitykset.Tilitysnro =
Tilitystiedot.Tilitysnro) INNER JOIN Asiakashinnat ON Tilitystiedot.Tuotenro
= Asiakashinnat.Tuotenro
GROUP BY Autoilijat.[Yrityksen nimi], Tilitystiedot.Viikko,
Tilitystiedot.Tuotenro, Tilitystiedot.Määrä, Tilitystiedot.Hinta,
Tilitystiedot.Syöttöhinta, Asiakashinnat.Hinta;

--
Thankful for Your help!


"KARL DEWEY" kirjoitti:
Post your SQL.
Click to expand...
Click to expand...
 
Does it make any difference what the field names mean :) Well, I try to
translate...

I try to calculate the difference what we have got from our customers and
what we have payed to our drivers. I should have a table/report where there
are several weeks and sums too.

SQL:
SELECT Drivers.[Companyname], Paying.Week,
Paying.ProductID, Paying.Amount, Paying.Price,
Paying.Price2, Customerprices.Price,
Sum([Amount]*nz([Paying].[Price],0)+[Amount]*nz([Paying].[Price2],0)) AS
Payed, Sum([Amount]*[Customerprices].[Price]) AS Given
FROM ((Paying INNER JOIN Drivers ON Paying.DriverID=
Drivers.DriverID) INNER JOIN Paying ON Paying.PayingID=
Paying.PayingID) INNER JOIN Customerprices ON Paying.ProductID
= Customerprices.ProductID
GROUP BY Drivers.[Companyname], Tilitystiedot.Viikko,
Tilitystiedot.Tuotenro, Tilitystiedot.Määrä, Tilitystiedot.Hinta,
Tilitystiedot.Syöttöhinta, Asiakashinnat.Hinta;Paying.Week,
Paying.ProductID, Paying.Amount, Paying.Price,
Paying.Price2, Customerprices.Price


--
Thankful for Your help!


"KARL DEWEY" kirjoitti:
Being as how I do not speak Alute, I can not translate what you said you want
for a crosstab query from your select query. More please.

Tellu said:
Here's my SQL. Iis it too difficult in finnish?

SELECT Autoilijat.[Yrityksen nimi], Tilitystiedot.Viikko,
Tilitystiedot.Tuotenro, Tilitystiedot.Määrä, Tilitystiedot.Hinta,
Tilitystiedot.Syöttöhinta, Asiakashinnat.Hinta,
Sum([määrä]*nz([tilitystiedot].[Hinta],0)+[määrä]*nz([tilitystiedot].[syöttöhinta],0)) AS Tilitetty, Sum([määrä]*[asiakashinnat].[hinta]) AS Laskutettu
FROM ((Tilitykset INNER JOIN Autoilijat ON Tilitykset.Autoilijanro =
Autoilijat.Autoilijanro) INNER JOIN Tilitystiedot ON Tilitykset.Tilitysnro =
Tilitystiedot.Tilitysnro) INNER JOIN Asiakashinnat ON Tilitystiedot.Tuotenro
= Asiakashinnat.Tuotenro
GROUP BY Autoilijat.[Yrityksen nimi], Tilitystiedot.Viikko,
Tilitystiedot.Tuotenro, Tilitystiedot.Määrä, Tilitystiedot.Hinta,
Tilitystiedot.Syöttöhinta, Asiakashinnat.Hinta;

--
Thankful for Your help!


"KARL DEWEY" kirjoitti:
Post your SQL.

:

Hi!

I have a problem that I can't solve myself, but I know it can be solved in
Access. Can someone help me?

I should get a report like this:

Company1 Company 2 Company 3
Week 40
Given
sum(given)
Payed
sum(payed)
Given-payed
sum(given-payed)

Week 41
same things

Week 42
and so one

every company sum(given-payed)

Given and payed are calculated in a query. I have tried to make a report
where there're several columns, but i've met some problems. If for example
company 1 haven't got any details in week 41, there should be empty space so
that the details of certain week are in the same row. And I don't know how to
calculate the sum of rows. The row headings should come only once to the left
.

I tried to export the report to Excel but it didn't maintain the structure.
So, what can be done?

Tellu
Click to expand...
Click to expand...
Click to expand...
 
I called your query Tellu and create an union query named Tellu_1 and then
that as source for a crosstab query. I had to use "Transaction" and
"Dollars" which you can revise.

Tellu_1 ---
SELECT Tellu.Viikko, Tellu.[Yrityksen nimi], "Tilitetty" AS [Transaction],
Tellu.Tilitetty AS Dollars
FROM Tellu
UNION SELECT Tellu.Viikko, Tellu.[Yrityksen nimi], "Laskutettu" AS
[Transaction], Tellu.Laskutettu AS Dollars
FROM Tellu
UNION SELECT Tellu.Viikko, Tellu.[Yrityksen nimi], "Laskutettu - Tilitetty"
AS [Transaction], [Tellu].[Laskutettu]-[Tellu].[Tilitetty] AS Dollars
FROM Tellu;

TRANSFORM Sum(Tellu_1.Dollars) AS SumOfDollars
SELECT Tellu_1.Viikko, Tellu_1.Transaction, Sum(Tellu_1.Dollars) AS [Total
Of Dollars]
FROM Tellu_1
GROUP BY Tellu_1.Viikko, Tellu_1.Transaction
PIVOT Tellu_1.[Yrityksen nimi];


Tellu said:
Does it make any difference what the field names mean :) Well, I try to
translate...

I try to calculate the difference what we have got from our customers and
what we have payed to our drivers. I should have a table/report where there
are several weeks and sums too.

SQL:
SELECT Drivers.[Companyname], Paying.Week,
Paying.ProductID, Paying.Amount, Paying.Price,
Paying.Price2, Customerprices.Price,
Sum([Amount]*nz([Paying].[Price],0)+[Amount]*nz([Paying].[Price2],0)) AS
Payed, Sum([Amount]*[Customerprices].[Price]) AS Given
FROM ((Paying INNER JOIN Drivers ON Paying.DriverID=
Drivers.DriverID) INNER JOIN Paying ON Paying.PayingID=
Paying.PayingID) INNER JOIN Customerprices ON Paying.ProductID
= Customerprices.ProductID
GROUP BY Drivers.[Companyname], Tilitystiedot.Viikko,
Tilitystiedot.Tuotenro, Tilitystiedot.Määrä, Tilitystiedot.Hinta,
Tilitystiedot.Syöttöhinta, Asiakashinnat.Hinta;Paying.Week,
Paying.ProductID, Paying.Amount, Paying.Price,
Paying.Price2, Customerprices.Price


--
Thankful for Your help!


"KARL DEWEY" kirjoitti:
Being as how I do not speak Alute, I can not translate what you said you want
for a crosstab query from your select query. More please.

Tellu said:
Here's my SQL. Iis it too difficult in finnish?

SELECT Autoilijat.[Yrityksen nimi], Tilitystiedot.Viikko,
Tilitystiedot.Tuotenro, Tilitystiedot.Määrä, Tilitystiedot.Hinta,
Tilitystiedot.Syöttöhinta, Asiakashinnat.Hinta,
Sum([määrä]*nz([tilitystiedot].[Hinta],0)+[määrä]*nz([tilitystiedot].[syöttöhinta],0)) AS Tilitetty, Sum([määrä]*[asiakashinnat].[hinta]) AS Laskutettu
FROM ((Tilitykset INNER JOIN Autoilijat ON Tilitykset.Autoilijanro =
Autoilijat.Autoilijanro) INNER JOIN Tilitystiedot ON Tilitykset.Tilitysnro =
Tilitystiedot.Tilitysnro) INNER JOIN Asiakashinnat ON Tilitystiedot.Tuotenro
= Asiakashinnat.Tuotenro
GROUP BY Autoilijat.[Yrityksen nimi], Tilitystiedot.Viikko,
Tilitystiedot.Tuotenro, Tilitystiedot.Määrä, Tilitystiedot.Hinta,
Tilitystiedot.Syöttöhinta, Asiakashinnat.Hinta;

--
Thankful for Your help!


"KARL DEWEY" kirjoitti:

Post your SQL.

:

Hi!

I have a problem that I can't solve myself, but I know it can be solved in
Access. Can someone help me?

I should get a report like this:

Company1 Company 2 Company 3
Week 40
Given
sum(given)
Payed
sum(payed)
Given-payed
sum(given-payed)

Week 41
same things

Week 42
and so one

every company sum(given-payed)

Given and payed are calculated in a query. I have tried to make a report
where there're several columns, but i've met some problems. If for example
company 1 haven't got any details in week 41, there should be empty space so
that the details of certain week are in the same row. And I don't know how to
calculate the sum of rows. The row headings should come only once to the left
.

I tried to export the report to Excel but it didn't maintain the structure.
So, what can be done?

Tellu
Click to expand...
Click to expand...
Click to expand...
 
Karl, Thanks' a lot! Your queries work even in my database. Somehow I
understand what you´ve made but couldn't ever do the same without someone
telling what to do. Have a nice day!

Tellu
--
Thankful for Your help!


"KARL DEWEY" kirjoitti:
I called your query Tellu and create an union query named Tellu_1 and then
that as source for a crosstab query. I had to use "Transaction" and
"Dollars" which you can revise.

Tellu_1 ---
SELECT Tellu.Viikko, Tellu.[Yrityksen nimi], "Tilitetty" AS [Transaction],
Tellu.Tilitetty AS Dollars
FROM Tellu
UNION SELECT Tellu.Viikko, Tellu.[Yrityksen nimi], "Laskutettu" AS
[Transaction], Tellu.Laskutettu AS Dollars
FROM Tellu
UNION SELECT Tellu.Viikko, Tellu.[Yrityksen nimi], "Laskutettu - Tilitetty"
AS [Transaction], [Tellu].[Laskutettu]-[Tellu].[Tilitetty] AS Dollars
FROM Tellu;

TRANSFORM Sum(Tellu_1.Dollars) AS SumOfDollars
SELECT Tellu_1.Viikko, Tellu_1.Transaction, Sum(Tellu_1.Dollars) AS [Total
Of Dollars]
FROM Tellu_1
GROUP BY Tellu_1.Viikko, Tellu_1.Transaction
PIVOT Tellu_1.[Yrityksen nimi];


Tellu said:
Does it make any difference what the field names mean :) Well, I try to
translate...

I try to calculate the difference what we have got from our customers and
what we have payed to our drivers. I should have a table/report where there
are several weeks and sums too.

SQL:
SELECT Drivers.[Companyname], Paying.Week,
Paying.ProductID, Paying.Amount, Paying.Price,
Paying.Price2, Customerprices.Price,
Sum([Amount]*nz([Paying].[Price],0)+[Amount]*nz([Paying].[Price2],0)) AS
Payed, Sum([Amount]*[Customerprices].[Price]) AS Given
FROM ((Paying INNER JOIN Drivers ON Paying.DriverID=
Drivers.DriverID) INNER JOIN Paying ON Paying.PayingID=
Paying.PayingID) INNER JOIN Customerprices ON Paying.ProductID
= Customerprices.ProductID
GROUP BY Drivers.[Companyname], Tilitystiedot.Viikko,
Tilitystiedot.Tuotenro, Tilitystiedot.Määrä, Tilitystiedot.Hinta,
Tilitystiedot.Syöttöhinta, Asiakashinnat.Hinta;Paying.Week,
Paying.ProductID, Paying.Amount, Paying.Price,
Paying.Price2, Customerprices.Price


--
Thankful for Your help!


"KARL DEWEY" kirjoitti:
Being as how I do not speak Alute, I can not translate what you said you want
for a crosstab query from your select query. More please.

:

Here's my SQL. Iis it too difficult in finnish?

SELECT Autoilijat.[Yrityksen nimi], Tilitystiedot.Viikko,
Tilitystiedot.Tuotenro, Tilitystiedot.Määrä, Tilitystiedot.Hinta,
Tilitystiedot.Syöttöhinta, Asiakashinnat.Hinta,
Sum([määrä]*nz([tilitystiedot].[Hinta],0)+[määrä]*nz([tilitystiedot].[syöttöhinta],0)) AS Tilitetty, Sum([määrä]*[asiakashinnat].[hinta]) AS Laskutettu
FROM ((Tilitykset INNER JOIN Autoilijat ON Tilitykset.Autoilijanro =
Autoilijat.Autoilijanro) INNER JOIN Tilitystiedot ON Tilitykset.Tilitysnro =
Tilitystiedot.Tilitysnro) INNER JOIN Asiakashinnat ON Tilitystiedot.Tuotenro
= Asiakashinnat.Tuotenro
GROUP BY Autoilijat.[Yrityksen nimi], Tilitystiedot.Viikko,
Tilitystiedot.Tuotenro, Tilitystiedot.Määrä, Tilitystiedot.Hinta,
Tilitystiedot.Syöttöhinta, Asiakashinnat.Hinta;

--
Thankful for Your help!


"KARL DEWEY" kirjoitti:

Post your SQL.

:

Hi!

I have a problem that I can't solve myself, but I know it can be solved in
Access. Can someone help me?

I should get a report like this:

Company1 Company 2 Company 3
Week 40
Given
sum(given)
Payed
sum(payed)
Given-payed
sum(given-payed)

Week 41
same things

Week 42
and so one

every company sum(given-payed)

Given and payed are calculated in a query. I have tried to make a report
where there're several columns, but i've met some problems. If for example
company 1 haven't got any details in week 41, there should be empty space so
that the details of certain week are in the same row. And I don't know how to
calculate the sum of rows. The row headings should come only once to the left
.

I tried to export the report to Excel but it didn't maintain the structure.
So, what can be done?

Tellu
Click to expand...
Click to expand...
Click to expand...
 

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

Display a weekending date even if there is no data 5
How to add an additional summed up Quantity 8
Crosstab: show contents of field rather than sum? 12
CrossTab Query Access Report - 1
2 subtotals from one report 3
RE: You tried to execute a query that does not include the specified e 0
Design of Payroll Report (Gross Pay >=$25) 1
Problem with Sum 1

Back
Top