Access SQL Query

[robin]

Hi all,

Using Access 2003

I'm a bit new to Access (told by my employer to 'learn it' last Monday)... anyway lack of any training aside, googling is going well and I'm getting my head round SQL....

I'm completely stumped on the following.....

I have a table as follows (not sure how best to format this)

Name,WorkGroup, Clinic
Mr Smith,Eye Specialist,9943 75
Mr Smith,General,3 24532
Mr Smith,Cataract, 2342532
Mr Jones,Antenatal ,234523
Mr Jones,Obstetric ,245666
Mr Doe,Ilizarov,45 3453
Mr Doe,Flat Feet,4363456
Mr Doe,Osteogenesi s,34564
Mr Doe,General,256 3245

I need this as follows

Name,WorkGroup1 ,Clinic1,Workgr oup2,Clinic2,Wo rkgroup3 etc...
Mr Smith,Eye Specialist,9943 75,General,3245 32,Cataract
Mr Jones,Antenatal ,234523,Obstetr ic,245666
Mr Doe,Ilizarov,45 3453,Flat Feet,4363456,Os teogenesis


All the 'solutions' I have found online fall down on one of the following two areas...

They assume small number of 'Names' (i.e. Smith Jones and Doe are the ONLY employees.... we have more than 270)
They assume a small group of repeated 'Workgroups' (again same problem)

I hope someone is able to help.

I'm basically looking for a bit of SQL code for this. I could do it in Perl quite nicely, but the database needs to be stand alone for distribution purposes.


Very very grateful for any assistance

Robin

[MMcCarthy]

Create a query using the access design window. Drag in multiple copies of the table. Create a relationship between the first instance of the table on Workgroup and each of the other instances (but nothing between the other instances)

Drag down the Name column only from the first instance of the table.
For each of the other instances drag down Workgroup and Clinic. You will need the same number of instances as Workgroups. You will need to rename each of the columns

e.g. Workgroup1: [TableName_1].[Workgroup] and Clinic1: [TableName_1].[Clinic]

Now in the Criteria for Workgroup1 put "General" and in the Criteria for Workgroup2 put "Eye Specialist" etc.

Hi all,

Using Access 2003

I'm a bit new to Access (told by my employer to 'learn it' last Monday)... anyway lack of any training aside, googling is going well and I'm getting my head round SQL....

I'm completely stumped on the following.....

I have a table as follows (not sure how best to format this)

Name,WorkGroup, Clinic
Mr Smith,Eye Specialist,9943 75
Mr Smith,General,3 24532
Mr Smith,Cataract, 2342532
Mr Jones,Antenatal ,234523
Mr Jones,Obstetric ,245666
Mr Doe,Ilizarov,45 3453
Mr Doe,Flat Feet,4363456
Mr Doe,Osteogenesi s,34564
Mr Doe,General,256 3245

I need this as follows

Name,WorkGroup1 ,Clinic1,Workgr oup2,Clinic2,Wo rkgroup3 etc...
Mr Smith,Eye Specialist,9943 75,General,3245 32,Cataract
Mr Jones,Antenatal ,234523,Obstetr ic,245666
Mr Doe,Ilizarov,45 3453,Flat Feet,4363456,Os teogenesis


All the 'solutions' I have found online fall down on one of the following two areas...

They assume small number of 'Names' (i.e. Smith Jones and Doe are the ONLY employees.... we have more than 270)
They assume a small group of repeated 'Workgroups' (again same problem)

I hope someone is able to help.

I'm basically looking for a bit of SQL code for this. I could do it in Perl quite nicely, but the database needs to be stand alone for distribution purposes.


Very very grateful for any assistance

Robin

[robin]

Hi mmccarthy, thanks very much for offering your help.

Now in the Criteria for Workgroup1 put "General" and in the Criteria for Workgroup2 put "Eye Specialist" etc.

Ok, before I try that will that not leave me with a very sparse table?

Would I need to individually put in each of the 264 different types of Workgroup?

If a new workgroup is created it won't show up until it is added to the criteria?

Sorry for so many questions, I'm mostly looking for a highly maintainable option. The data will always be in the same format, but that is the only thing about it that is certain. I'm begginning to think that this is beyond a simple SQL query and I might need some scripted action.

Robin

[MMcCarthy]

I can't think of a less complicated way of doing it at the moment. Maybe someone else will be able to.

Hi mmccarthy, thanks very much for offering your help.




Ok, before I try that will that not leave me with a very sparse table?

Would I need to individually put in each of the 264 different types of Workgroup?

If a new workgroup is created it won't show up until it is added to the criteria?

Sorry for so many questions, I'm mostly looking for a highly maintainable option. The data will always be in the same format, but that is the only thing about it that is certain. I'm begginning to think that this is beyond a simple SQL query and I might need some scripted action.

Robin

[PEB]

Hi

Do you reelly think that somebody can read a table with more than 528 columns? I'm estonished that somebody can want this thing!!!!

When my tables that are visualized have more than 12 columns the users are upset...

Reelly I'm not sure that Access can vizualize more than 255 columns as a query result...

I'm tring it now...

In fact it is impossible in Access to have such columns... More than 255

Sorry

Try in Excel...

:)

[robin]

Hi

Do you reelly think that somebody can read a table with more than 528 columns? I'm estonished that somebody can want this thing!!!!

Lol!! Thanks, luckily the someone is a computer (clinic management). So hopefully they won't complain.

I sorted it in Perl. Thing is I'm moving departments soon and it would have been better to have a one step solution for the people who will have to use it later.

Anyway... Access does have some limits I guess.

Rich