Crosstab (1 Viewer)

[salgergawi]

Hello,
I need an idea to help to create an cross table I have done an normal query and from the query i need to do an cross query.

The first Query which will be call for the crosstab contain:

Code

SELECT Estebak.Attacking, Estebak.Defenseing, Estebak.Nothing, Unites_Name.Unite_Name
FROM (Attack LEFT JOIN Estebak ON Attack.ID = Estebak.attack_id) LEFT JOIN Unites_Name ON Attack.Unite_ID = Unites_Name.Unite_ID;

col1 | col2 | col3 | col4
Unite_Name |Attacking |Defenseing |Nothing

what i need in the crosstab to get the Columns and Rows with Unite_Name
and the values of (Attacking, Defenseing & Nothing) to be fill in the table as the follwing
if Attacking value is true to set Value Att
if Defenseing value is true to set Def
if Nothing value is true to set NA

my code is:

TRANSFORM Query1.Unite_Name
SELECT Query1.Attacking
FROM Query1
GROUP BY Query1.Unite_Name
PIVOT Query1.Attacking;

 

[JHB]

Show some sample data in an Excel Sheet or database and also how you want the result.

 

[salgergawi]

Can i send you to you e-mail directly because i have only one post and cannot add anykind of line or image

 

[salgergawi]

my mail address is salgergawi @ yahoo .com

 

[salgergawi]

as well i want to know could we add an crosstab query to the reports?
and how could that be done?

 

[salgergawi]

My main Query which i will the crosstab take is:

The result which will be on the crosstab is as following:

That is what i am looking for as an example

Note: i have remove the link because i can't attached any kind of link or image because i only one post.
Hope can be help in that

 

[JHB]

If you zip your attached you can post it before 10 post.
Out of the shown data in the main query, I can't image how get the shown cross tab result ex. by Ariete, because there are no values, but maybe there are missing some data?

 

[salgergawi]

If you zip your attached you can post it before 10 post.
Out of the shown data in the main query, I can't image how get the shown cross tab result ex. by Ariete, because there are no values, but maybe there are missing some data?

that is what i want exactly to get the result the value or text from the three columns (Attacking, Defenseing and Nothing) to get it into the cross table, and to have the column and row names as "unite_name". is that possible.

 

[JHB]

Sorry I can't see the logic, see attached picture.

 

[salgergawi]

You are right, the image is just an example on Excel and i want to get the same resualt as it is in the image.

 

[salgergawi]

Sorry I can't see the logic, see attached picture.

Sorry i have misunderstand

What i am looking to have a table as it is in this image

and while we imagen we have all our data in this image

for example all the columns of attacking , defenseing and nothing are filled how can i do an table using SQL to get all this informations as this image?

Please advice ....
I think using PIVOT ?!
so if the value is in column Attacking to replace it to AT
if the value is in column Defensing replace the value to DF
and if nothing to replace it to NA

 

[salgergawi]

Hello, i was asking for crosstab
please have a look for my example
http://speedy.sh/fCkWk/Example.xlsx

two tabs,
Tab: MainQuery:
Explain the main query which have been done to get this table.

Tab2, Crosstab:
is an example of what i want, please go to row 30 under unite "Osorio" so you can see what i mean ...
I have done only one record for example.

 

[JHB]

Sorry - I didn't downloaded you file because it want me to install some program, and I don't want that.
Below is a link how you post before 10 post.
http://www.access-programmers.co.uk/forums/showthread.php?t=212124

If you data has looked like below then I could see some logic, (but it isn't ...).

 

[salgergawi]

ok i got only a snap from the data which i mean,
acording to my last post

That is an real example from what i want which i have made it manual on excel,

Tab1 is as following

Tab2:

hope that will help

 

[JHB]

Hmm - the data looks more or less as the sample I showed in post #13, so I've have attached a small database with a solution.
Use a crosstab query to create an empty matrix with the different Unit_name as rows and columns.
Then run a make table query called "CreateTableQuery", on the crosstab query called "CreateColumnAndRow", to create a table called "FinalTable".
Then use a recordset to fill the created matrix with "AT", "DF" and "N/A" depending of if there are some values in the "Attacking", "Defenseing" or "Nothing".
Try it and see if it fulfill your requirement.
Open the only form in the database an hit the button.
The result is shown in table "FinalTable".

Why wasn't the sample data you showed here not complete, it could have saved a lot of time.

 

[salgergawi]

Hmm - the data looks more or less as the sample I showed in post #13, so I've have attached a small database with a solution.
Use a crosstab query to create an empty matrix with the different Unit_name as rows and columns.
Then run a make table query called "CreateTableQuery", on the crosstab query called "CreateColumnAndRow", to create a table called "FinalTable".
Then use a recordset to fill the created matrix with "AT", "DF" and "N/A" depending of if there are some values in the "Attacking", "Defenseing" or "Nothing".
Try it and see if it fulfill your requirement.
Open the only form in the database an hit the button.
The result is shown in table "FinalTable".

Why wasn't the sample data you showed here not complete, it could have saved a lot of time.

Exactly that is what i am looking for....
Many thanx for this, but when open the form and click i got the following image where i am the only one who open the file.

 

[JHB]

Ok - I'm not getting an error when I run it.
I've attached a 2007 - file format instead, maybe it solve the problem.
Else try by creating a new database and import everything into it.

 

[salgergawi]

Hi again
when I run the "CreateCrossTable" it works but receive an error, i try to follow up but i do not know what could it be.

 

[JHB]

1. problem, there are spaces in the names after the last letter, it can be solved by a RTrim
2. problem, Arabian letters like "يو إس إس جورج بوش" it is "converted" in MS-Access to "????? ?????", maybe it can be solved, I don't know, else you are pushed back to start.
Solution for problem 1:

      If Not IsNull(rst![Attacking]) Then
        rstInsert(RTrim(rst![Attacking])) = "AT"
      End If
      If Not IsNull(rst![Defenseing]) Then
        rstInsert(RTrim(rst![Defenseing])) = "DF"
      End If
      If Not IsNull(rst![Nothing]) Then
        rstInsert(RTrim(rst![Nothing])) = "N/A"
      End If

 

[JHB]

It seems for me as you have some names which are not in the "Unites_name" table, do they not have to be there, (it is why problem no. 2 occurs.)??
If not, the query "CreateColumnAndRow" must be constructed in another way and the same for the query "UniteName_Eshtibak"
The below text is in the "Estebak" table under "Attacking", and I couldn't find it in the "Unites_name" table!
صواريخ مضادة للطائرات