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,994375
Mr Smith,General,324532
Mr Smith,Cataract,2342532
Mr Jones,Antenatal,234523
Mr Jones,Obstetric,245666
Mr Doe,Ilizarov,453453
Mr Doe,Flat Feet,4363456
Mr Doe,Osteogenesis,34564
Mr Doe,General,2563245
I need this as follows
Name,WorkGroup1,Clinic1,Workgroup2,Clinic2,Workgro up3 etc...
Mr Smith,Eye Specialist,994375,General,324532,Cataract
Mr Jones,Antenatal,234523,Obstetric,245666
Mr Doe,Ilizarov,453453,Flat Feet,4363456,Osteogenesis
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
5 2769
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,994375
Mr Smith,General,324532
Mr Smith,Cataract,2342532
Mr Jones,Antenatal,234523
Mr Jones,Obstetric,245666
Mr Doe,Ilizarov,453453
Mr Doe,Flat Feet,4363456
Mr Doe,Osteogenesis,34564
Mr Doe,General,2563245
I need this as follows
Name,WorkGroup1,Clinic1,Workgroup2,Clinic2,Workgro up3 etc...
Mr Smith,Eye Specialist,994375,General,324532,Cataract
Mr Jones,Antenatal,234523,Obstetric,245666
Mr Doe,Ilizarov,453453,Flat Feet,4363456,Osteogenesis
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
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
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 1,418
Expert 1GB
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...
:)
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
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Frnak McKenney |
last post by:
Back when computer dinosaurs roamed the earth and the precursors to
today's Internet were tiny flocks of TDMs living symbiotically with
the silicon giants, tracking access to data processing...
|
by: Joris Kempen |
last post by:
Hi people,
I know that the question has come around sometimes:
How to open an Access Report using ASP and export it to for example
RTF.
I'm trying to implement the first method of David...
|
by: Sean C. |
last post by:
Helpful folks,
Most of my previous experience with DB2 was on s390 mainframe systems
and the optimizer on this platform always seemed very predictable and
consistent. Since moving to a WinNT/UDB...
|
by: bhbgroup |
last post by:
I have a query on one large table. I only add one condition, i.e. a
date (the SQL reads like 'where date > parameterdate'.
This query is rather quick if 'parameterdate' is either explicitly...
|
by: Neil |
last post by:
We are running an Access 2000 MDB with a SQL 7 back end. Our network guy is
upgrading to Windows Server 2003 and wants to upgrade Office and SQL Server
at the same time. We're moving to SQL Server...
|
by: tt40 |
last post by:
Anyone know how to prevent Access 2002 from automatically breaking all
the incorrect joins in a query and then automatically saving the broken
query?
This is what I would call stupid design...
|
by: InnoCreate |
last post by:
Hi everyone.
I've recently written a classic asp website which uses an MS Access
datasource. I know this is less than an ideal data source as it has
limited functionality. I have a search form on...
|
by: mnjkahn via AccessMonster.com |
last post by:
I'm running Access 2003, modifying a query that has over 45 fields. When I
right click on the field name in Query Design View, and then click Build,
Access crashes before the Build window...
|
by: jsacrey |
last post by:
Hey everybody, got a secnario for ya that I need a bit of help with.
Access 97 using linked tables from an SQL Server 2000 machine.
I've created a simple query using two tables joined by one...
|
by: magickarle |
last post by:
Hi, I got a pass-through query (that takes about 15 mins to process)
I would like to integrate variables to it.
IE: something simple:
Select EmplID from empl_Lst
where empl_lst.timestamp between...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
| |