combining tables into query

[Fre Timmerman]

hi,

I have 2 tables:

1 with participants:

ParticipantID Name
1 John
2 Mike
3 Elena
4 Anna


and 1 with workshops they participate:

ID Participant Hour Workshop
1 1 14 javascript
2 1 15 router
3 1 16 security
4 2 14 router
5 2 15 javascript
6 2 16 security
7 3 14 javascript
8 3 15 security
9 3 16 router
10 4 14 javascript
11 4 15 router
12 4 16 security


and I want to make a query looking like this:

Name 14h 15h 16h
John javascript router security
Mike router javascript security
Elena javascript security router
Anna javascript router security


but i don't find the right arguments
should anyone be able to give me the right criteria please?

Greetings..

 

[Allen Browne]

Create a Crosstab query, where:
- [Name] is the Row Heading,
- [Hour] is the Column Heading, and
- [Workshop] is the Value.

Crosstab is on the Query menu (in query design.)

Note that there are several potential problems here:
1. There could be more than one Worship in any hour for any participant.

2. Fields named Name and Hour can give you problems. Forms and reports have
a Name property, so you might find Access showing the name of the
form/report intead of the field called Name. Similarly, Hour() is a VBA
function, and can cause errors. Consider renaming the fields to
ParticipantName, WorkHour, or something.

Once you get into crosstabs, here's some other general hints:
Crosstab query techniques
at:
http://allenbrowne.com/ser-67.html

 

[Fre Timmerman]

thank you for that field name hint,

but it won't work exactly like i want it to work the way you said.

i assume that i have to fill in "first" in the row 'total' at the query
design?
i have been looking for it for a while, and when i do that, the outcome is
fine.

thank you a lot for the help.

Create a Crosstab query, where:
- [Name] is the Row Heading,
- [Hour] is the Column Heading, and
- [Workshop] is the Value.

Crosstab is on the Query menu (in query design.)

Note that there are several potential problems here:
1. There could be more than one Worship in any hour for any participant.

2. Fields named Name and Hour can give you problems. Forms and reports
have a Name property, so you might find Access showing the name of the
form/report intead of the field called Name. Similarly, Hour() is a VBA
function, and can cause errors. Consider renaming the fields to
ParticipantName, WorkHour, or something.

Once you get into crosstabs, here's some other general hints:
Crosstab query techniques
at:
http://allenbrowne.com/ser-67.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

hi,

I have 2 tables:

1 with participants:

ParticipantID Name
1 John 2 Mike
3 Elena
4 Anna


and 1 with workshops they participate:

ID Participant Hour Workshop
1 1 14 javascript
2 1 15 router
3 1 16 security
4 2 14 router
5 2 15 javascript
6 2 16 security
7 3 14 javascript
8 3 15 security
9 3 16 router
10 4 14 javascript
11 4 15 router
12 4 16 security


and I want to make a query looking like this:

Name 14h 15h 16h
John javascript router security
Mike router javascript security
Elena javascript security router
Anna javascript router security


but i don't find the right arguments
should anyone be able to give me the right criteria please?

Greetings..