access and graphic performance of data

[ttsz]

Hello!
i have got a problem:
I need to do sth like that: (i know that this is made by excel but it is only example)

i have got tabels:
table: "szkoleni"

id	imię	nazwisko	inne info
1	Adam	Kowalski
2	Jan	Nowak
3	Paweł	Pawłowski

and 3 tables : q, C and F

id	nazwisko	ćwiczenie
1	Kowalski	q1001
2	Pawłowski	q1001
3	Kowalski	q1002
4	kowalski	q1003

tabela c

id	nazwisko	ćwiczenie
1	Kowalski	c1001
2	Pawłowski	c1001
3	Kowalski	c1002
4	Nowak	c1001

tabela f

id	nazwisko	ćwiczenie
1	Kowalski	f1001
2	Kowalski	f1002
3	Kowalski	f1003
4	Kowalski	f1004

and now i want to get some thing like that:

kowalski

q1001

q1002

q1003

q1004

c1001

c1002

c1003

c1004

f1001

f1002

f1003

f1004

f1005

Pawłowski

q1001

q1002

q1003

q1004

c1001

c1002

c1003

c1004

f1001

f1002

f1003

f1004

f1005

Nowak

q1001

q1002

q1003

q1004

c1001

c1002

c1003

c1004

f1001

f1002

f1003

f1004

f1005

To do it i think i should use conditional formating, but i cannot handle this. I think i should "find if record exist" in tables but im stuck.
The most important for me is to make several squers for evry person from table "szkoleni" whit the same numbers q1001,q1002,q1003 etc and make the squers to hange colour if the record is in table q , f or c.
I hope you will help me.


Sorry for my pure english:/ but i didnt use this language for yers

[isladogs]

If I understand you correctly, you need a crosstab query. There is a wizard to do this.
However, Access is not designed to create diagrams like the one you displayed.
Perhaps use an organisation chart in Word or PowerPoint?

[ttsz]

I know that acces is not designed to this work but i have to make this on it. If I use cross tab query i get only highest or lowest value not all values from tree tables:/

[isladogs]

Ah I see.
It may be that you could have done this using pivot tables/charts but that feature was deprecated in Access 2013.
As you have A2016, that option isn't available to you.

Perhaps you can emulate it, using several crosstab queries (using First, Last, Max, Min).
You can't run a UNION on crosstab queries so the alternative would be to append each of the results to a new table.
Even if it works, its horribly messy
Suggest you stick to using Excel

Good luck

[ttsz]

hello!
Thank you for all replys, but i dont know how to make EXCEL self updating, my app will be use by almost cavemans and it have to be as simple to use as posible. i tried to use conditional formating and put there Name and "number" (name = xxx & number = q1001 )
but it lost one of condytion. I dont believe it is imposible to make it in acc in simple way. At last i can add that numbers like q1001 etc. are the same for all "szkoleni", and all fo then are known right now, so i can make txtboxy's that will be in records (like q1001 to q1012 and the same to f and c).
i hope that my post is understendable...

[isladogs]

How about this



To reach this stage, I did the following
1. Created a UNION query (qryUnion) combining tables q,C & F for converting into a crosstab

2. Created a select query (qryCombined) from the union joined to table szkoleni
The select query was necessary as you can't create a crosstab from a union query
I added the table to get the extra row heading field (imie) needed for the crosstab

3. You can't add formatting to a crosstab query so I created a new select query (qryFinalFormatted) based on the crosstab
I then formatted the text as bold, green BUT adding format setting '[Black]@' to the first two columns!

Simple!!!!

See attached

[ttsz]

its amazing! thank you very much, you are almost access god!

[isladogs]

LOL
The 'demigod' says you're welcome.