How do I make a query with dynamic number of coloumns?

E

Ebbe

Hey!

I have asked this question as an additional question a day ago.
But I thought it was better to let this question stand alone.

The mission is to make an overview over persons bookings of activities,
where persons are rows and personname and activities are columns.
The number of activities are not fixed, but is given by the table:
tblActivity

The booking is marked with '1' or a '*' or a True (What is easiest to
produce)

I have 3 tables with persons, activities and bookings.

tblPerson:
strPerName;strGroup
John;Th
Kenn;Th
Mary;Th
Nya;Hy

tblActivity:
strActName
Kodes
Nature
Fire
Tools
Movement

tblBooking:
strPerName;strActName
John;Kodes
John;Tools
Kenn;Nature
Kenn;Tools
Mary;Kodes
Mary;Nature
Nya;Movement
Nya;Fire

I wish query returning:
qryOverview:
strPerName;Kodes;Nature;Fire;Tools;Movement
John;1;0;0;1;0
Kenn;0;1;0;1;0
Mary;1;1;0;0;0
Nya;0;0;1;0;1

The 1's and 0's could be True/False or '*'/'' or something else representing
"Booked"/"Not booked"

Can I come around with this without generate a query using VBA?
I am not afraid of using VBA, but it would be easier.

Ebbe
 
J

Jeff Boyce

Embedding data in your tables' field names results in a data structure that
works ... for a spreadsheet! Access is a relational database, and you'll
only frustrate yourself (and Access) if you try to make it perform like a
spreadsheet.

Is there a reason why you can't simply use a spreadsheet?

If you determine that you need to use Access' strengths, then you'll need to
learn about "normalization", determining the entity-relationship
characteristics of your data.

To recap the above comments, you describe a table that uses fields to hold
persons' names. A more normalized design would use person names as
contents/values IN fields, something like:

tblPerson
PersonID
FirstName
MiddleName
LastName
... (and other characteristics of an individual person, like
DateOfBirth)

I strongly recommend you revisit your data structure before you pursue your
design any further.
 
E

Ebbe

Hi Jeff

Why I focus on Access is that the data is prodused by Access.
The persons gives the activities a priority code and the system disiributes
the persons over the activities regarding to the priority.
The result is tblBooking.
One of the reports, I wish to produce is a spredsheet like overview over
witch persons has got twitch activities.

Do I understand you in that way, that you propose to let Access make a Excel
spreadsheet and then print this out?

Ebbe
 
M

MacDermott

I think you can get the results you want from a cross-tab query.
Read about them in your Access Help file, or learning medium of choice.

And to follow up on Jeff's line of thought -
you can easily save the results of that cross-tab query into an Excel
sheet for further use.
 
J

John Vinson

Can I come around with this without generate a query using VBA?
I am not afraid of using VBA, but it would be easier.
Click to expand...

Yes; a Crosstab Query using the activity as the Column Header and the
person as the Row Header will give you exactly this result. Use the
Crosstab query wizard, or look up Crosstab and/or TRANSFORM (the SQL
keyword for crosstab queries) in the online help.

John W. Vinson[MVP]
 
T

Tom Wickerath

Or try my crosstab tutorial, available at:
http://www.access.qbuilt.com/html/crosstab_queries.html



Tom
______________________________


Can I come around with this without generate a query using VBA?
I am not afraid of using VBA, but it would be easier.
Click to expand...

Yes; a Crosstab Query using the activity as the Column Header and the
person as the Row Header will give you exactly this result. Use the
Crosstab query wizard, or look up Crosstab and/or TRANSFORM (the SQL
keyword for crosstab queries) in the online help.

John W. Vinson[MVP]
 
J

Jeff Boyce

No, that was not my proposal. I was attempting to point out that what you
described sounds more like a spreadsheet than a relational database.

Why is your data in Access, if you want to do "spreadsheet-y" things with
it?
 
N

NILDA MANGUAL

Ebbe said:
Hey!

I have asked this question as an additional question a day ago.
But I thought it was better to let this question stand alone.

The mission is to make an overview over persons bookings of activities,
where persons are rows and personname and activities are columns.
The number of activities are not fixed, but is given by the table:
tblActivity

The booking is marked with '1' or a '*' or a True (What is easiest to
produce)

I have 3 tables with persons, activities and bookings.

tblPerson:
strPerName;strGroup
John;Th
Kenn;Th
Mary;Th
Nya;Hy

tblActivity:
strActName
Kodes
Nature
Fire
Tools
Movement

tblBooking:
strPerName;strActName
John;Kodes
John;Tools
Kenn;Nature
Kenn;Tools
Mary;Kodes
Mary;Nature
Nya;Movement
Nya;Fire

I wish query returning:
qryOverview:
strPerName;Kodes;Nature;Fire;Tools;Movement
John;1;0;0;1;0
Kenn;0;1;0;1;0
Mary;1;1;0;0;0
Nya;0;0;1;0;1

The 1's and 0's could be True/False or '*'/'' or something else representing
"Booked"/"Not booked"

Can I come around with this without generate a query using VBA?
I am not afraid of using VBA, but it would be easier.

Ebbe
Click to expand...
 
Top