Moving data to tabular format from list table

[Col]

Hi all,



I'm trying to extract information in an Access table into another table
which shows information in a tabular format.



For example the table I have consists of basically a list of staff names
along with skills/training they have undertaken for example;



Name Skill

Jenny Smith Access Basic

Jazz Parker Word Advanced

Jenny Smith Word Basic

Sharon Walters Word Advanced

Jenny Smith Excel Advanced

Jazz Parker Book Keeping



The resultant table I wish to produce from the above would be;



Name Skill Skill Skill

Jenny Smith Access Basic Word Basic Excel Advanced

Sharon Walters Word Advanced

Jazz Parker Word Advanced Book Keeping



Any help offered would be much appreciated.



Colin.

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Your result is usually not an acceptable table format. You should use
the display layer (VBA code or some reporting tool [Access ?]) to place
the Skills in the manner you want.

Here's a solution that returns the "Skills" in a comma-delimited string,
which you can display in a form, or do something else.

http://www.mvps.org/access/modules/mdl0004.htm

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBRRWgOYechKqOuFEgEQL1hACfRc2ZoKlzSRjdVZt3AkmlqQf8o58AoNFr
cAlN/qS5uv58FnUVOF+tZeMe
=Co55
-----END PGP SIGNATURE-----

 

[Neil Sunderland]

I'm trying to extract information in an Access table into another table
which shows information in a tabular format.

For example the table I have consists of basically a list of staff names
along with skills/training they have undertaken for example;
Name Skill
Jenny Smith Access Basic
Jazz Parker Word Advanced
Jenny Smith Word Basic
Sharon Walters Word Advanced
Jenny Smith Excel Advanced
Jazz Parker Book Keeping

The resultant table I wish to produce from the above would be;
Name Skill Skill Skill
Jenny Smith Access Basic Word Basic Excel Advanced
Sharon Walters Word Advanced
Jazz Parker Word Advanced Book Keeping

If you add another column to your table, you can use a CrossTab query:
staff_name skill_acquired date_acquired
Jenny Smith Access Basic 2006/09/22
Jazz Parker Word Advanced 2006/09/22
Jenny Smith Word Basic 2006/09/22
Sharon Walters Word Advanced 2006/09/22
Jenny Smith Excel Advanced 2006/09/22
Jazz Parker Book Keeping 2006/09/22

Then you can use this:
TRANSFORM Min(Skills.date_acquired) AS MinOfdate_acquired
SELECT Skills.staff_name
FROM Skills
GROUP BY Skills.staff_name
PIVOT Skills.skill_acquired

Which gets you this:
staff_name AccessBasic BookKeeping ExcelAdv WordAdv WordBasic
Jazz Parker 22/09 22/09
Jenny Smith 22/09 22/09 22/09
Sharon Walters 22/09

[nb - I've manually truncated the fields so the rows don't wrap!]

 

[Col]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Your result is usually not an acceptable table format. You should use
the display layer (VBA code or some reporting tool [Access ?]) to place
the Skills in the manner you want.

Here's a solution that returns the "Skills" in a comma-delimited string,
which you can display in a form, or do something else.

http://www.mvps.org/access/modules/mdl0004.htm

Thanks for the help, I've tried the module but get the following compile
error (I've tried this in Access versions 2000 and XP).

Compile Error
User-defined type not defined

Any ideas?

Regards,

Colin.

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

No idea. When that error happens click the debug button instead of the
End button. The debug window opens & highlights the offending line in
yellow.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBRRnVjYechKqOuFEgEQIv0gCfXnl3HGD5usQS6nOI5OoJxLFGMs8An3Tr
lA5/QeyqVLCmNwIzpMt+1pNl
=fUIE
-----END PGP SIGNATURE-----

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Your result is usually not an acceptable table format. You should use
the display layer (VBA code or some reporting tool [Access ?]) to place
the Skills in the manner you want.

Here's a solution that returns the "Skills" in a comma-delimited string,
which you can display in a form, or do something else.

http://www.mvps.org/access/modules/mdl0004.htm

Thanks for the help, I've tried the module but get the following compile
error (I've tried this in Access versions 2000 and XP).

Compile Error
User-defined type not defined

Any ideas?

Regards,

Colin.

 

[Col]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

No idea. When that error happens click the debug button instead of the
End button. The debug window opens & highlights the offending line in
yellow.
--

Apologies, should have told you the offending line it's

Dim db As Database

Regards,

Colin.

 

[MGFoster]

Apologies, should have told you the offending line it's

Dim db As Database

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You probably have an Access version after '97. Those versions
automatically defaulted to ActiveX Data Object (ADO). The above line
refers to a Data Access Object (DAO) object. To correct you have to set
the References to DAO.

Open a VBA module.
In the Menu bar click Tools > References.
In the dialog box that appears click the check box for:
Microsoft DAO 3.6 Object Library
Then click the OK button.

Now you can refer to DAO objects in the VBA modules.

If you want to be explicit about DAO objects change the line to this:

Dim db As DAO.Database
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBRRsy3IechKqOuFEgEQLm5QCgjDNGe12wkHOC1tXTxmhKF84iKv8AoPUw
L6eozVDkTiInyT4ismwVnLFu
=umCf
-----END PGP SIGNATURE-----

 

[Col]

Thank you, that solved the error but I cannot get the function to work.



The table is called Training, I'm trying to concatenate the field Skill on
every instance of a staff members pay number.



I've input the following into a query:



List: fConcatChild("Training","Pay Number","Skill","Long",[Pay Number])



Unfortunately the resulting 'List' field in the query is blank. I've checked
the field type for Pay Number and that's OK, I also changed the field name
from Pay Number to simply 'Pay' but still nothing.



However, there are no Primary Keys in my table as the Pay Number field needs
duplicates. Having said that I've taken a look at the Northwind DB as per
the example of the module URL you gave me and the Order Details table has
the OrderID field as a Primary field but it allows duplicates! In fact the
ProductID table is a Primary Key also, giving to PK's on one table!



I've tried looking in the Index Wizard in the table design screen to see how
it was done but cannot replicate it on my database.



I'm confused now as I always believed that only one PK was allowed per table
and that duplicates were not allowed at all.



Can you help in assisting me to get my Training table to allow multiple pay
numbers and the Pay Number field the PK.



Thank you.



Colin.

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

There is only ONE PK in the table [Order Details] and it is made up of
TWO columns. A Primary Key can be one, or MORE, columns.

I looks like you're calling the function from a Query, this has to be
handled very carefully. The example indicates it was intended to be
called from VBA code.

To call it from a query you'd have to have a query something like this
(this is in the SQL View of the query def):

SELECT DISTINCT [Pay Number], fConcatChild("Training","Pay
Number","Skill","Long",[Pay Number]) As List
FROM Training
WHERE ... put some criteria here, if you want ...

You only want distinct pay numbers from the table. Having duplicates
will probably cause problems - some rows (records) may be skipped, I'm
not sure.

If that doesn't work, please post an example of the SQL that you are
using.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBRRykcYechKqOuFEgEQKpaACgzHDD6VCL6/r3ycrszvLy5+jf7toAnitB
fWQ+6CQC4XWNFy7WuSUJ6rPf
=gnXe
-----END PGP SIGNATURE-----

 

[Jamie Collins]

If you want to be explicit about DAO objects change the line to this:

Dim db As DAO.Database

Is there any reason for not being explicit about DAO objects?

Jamie.

--