• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 304
  • Last Modified:

Having problems with SQL statement on spliting a cell in Access

I have a cell that populates from a linked SharPoint multislect field. In a Query I am tryin gto filter it so that I can group and sort in a report on each different Value in the cell.

For example:
The cell looks like
12/23/09#;12/24/09#;

I received a solution a week ago:
looks like yes...

select id, holidayDate frorm (
select id, splitNget(holiday, ";", 0) as holidayDate from tblholidays
union
select id, splitNget(holiday, ";", 1) from tblholidays
...
union
select id, splitNget(holiday, ";", 20) from tblholidays
) as x order by holidaydate, id

it will give you

11/27/09 Record 1
11/27/09 Record 2
11/27/09 Record 3
11/28/09 Record 1
11/28/09 Record 2
11/28/09 Record 3

But I have no idea where and how to add this. Do I use this in the report? do I use this in a Query and if I use it in a query do I add it a in teh crieria line.  I know this must be simple, but for some reason I can't get my heda around where to use this SQL statement.  I need to have this solved to produce a report for next week that will group and sort records based on the cell produced by teh Sharepiont multiselect field that doesn't group the report like
 #;12/22/09#12/23/09;#
 #;12/22/09#12/23/09;#12/24/09
0
u2babs
Asked:
u2babs
  • 6
  • 3
  • 2
2 Solutions
 
MikeTooleCommented:
When you create an Access query, the query editor stores it as a SQL statement. Usually you don't need to worry about it, you just design the query visually and let Access get on with the rest. If you want to look at the SQL statement Access created you choose
   View,SQL-View
However, in a situation like this, the SQL is too complicated to be designed visually, so you need to edit it directly in the SQL-View of the query.
To try it out just create a new query, cancel the Show-table dialog and choose SQL View - it will display a SQL editor box containing:
   SELECT;
Delete that and paste-in the suggested Select statement.
You can now run the query
(this assumes the table and column names are the correct ones)
0
 
u2babsAuthor Commented:
How would I add this to a union query?
I have several difference tables (generated and linked to SharePoint lists) and I have to use a union query SDL to allow me to run reports that combine all the reports. I then want to use the SQL statement above to have reports that sorts and filters on the multiselcet field.
I have tried to set up a test dB to reflect what I am trying to do.

Please see end of thread for sample database.

Open in new window

0
 
u2babsAuthor Commented:
By the way I have the wrong points on this I would like to offer the max, I thought iw as offering 1000 but guess one can't do that.
Do you also know how i can change my award points?
0
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
u2babsAuthor Commented:
Is this the correct Syntax, I am getting errors.
i can't find information on frorm use?

select id, holidayDate frorm (
select id, splitNget(holiday, ";", 0) as holidayDate from tblholidays
union
select id, splitNget(holiday, ";", 1) from tblholidays
...
union
select id, splitNget(holiday, ";", 20) from tblholidays
) as x order by holidaydate, id

it will give you
0
 
u2babsAuthor Commented:
OK
I think I got the where and order, but I am getting errors on the SplitNGet. Is that the right SQL for Access?  I am getting errors and can't get it to work.  If it works I should shee each persons information repeated based on the number of holidays they are working in the Holidays field.
 

SELECT [Cell], [Role-1], "" AS [Role-2], "" AS Remote, "" AS [On-Call], [POD], [BEMSID],
[A/P], [Full Name], Workday, [Work-Shift], [Holiday_Shift], [Holiday_Coverage], [Manager],
[Primary Phone], "" AS [Other Phone], SplitnGet([747-8 : All Items].[Holidays],";#",0) as [Holidays],
[SME/Coach], Title, Department
FROM [Test :  All Items]
UNION
SELECT [Cell], [Role-1], "" AS [Role-2], "" AS Remote, "" AS [On-Call], [POD], [BEMSID],
[A/P], [Full Name], Workday, [Work-Shift], [Holiday_Shift], [Holiday_Coverage], [Manager],
[Primary Phone], "" AS [Other Phone], SplitNGet([747-8 : All Items].[Holidays],";#",1) as [Holidays],
[SME/Coach], Title, Department
FROM [Test :  All Items]
UNION
SELECT [Cell], [Role-1], "" AS [Role-2], "" AS Remote, "" AS [On-Call], [POD], [BEMSID],
[A/P], [Full Name], Workday, [Work-Shift], [Holiday_Shift], [Holiday_Coverage], [Manager],
[Primary Phone], "" AS [Other Phone], SplitNGet([[747-8 : All Items].[Holidays],";#",2) as [Holidays],
[SME/Coach], Title, Department
FROM [Test :  All Items] UNION
SELECT [Cell], [Role-1], "" AS [Role-2], "" AS Remote, "" AS [On-Call], [POD], [BEMSID],
[A/P], [Full Name], Workday, [Work-Shift], [Holiday_Shift], [Holiday_Coverage], [Manager],
[Primary Phone], "" AS [Other Phone], SplitNGet([747-8 : All Items].[Holidays],";#",3) as [Holidays],
[SME/Coach], Title, Department
FROM [Test :  All Items]
UNION
SELECT [Cell], [Role-1], "" AS [Role-2], "" AS Remote, "" AS [On-Call], [POD], [BEMSID],
[A/P], [Full Name], Workday, [Work-Shift], [Holiday_Shift], [Holiday_Coverage], [Manager],
[Primary Phone], "" AS [Other Phone], SplitNGet( [747-8 : All Items].[Holidays],";#",4) as [Holidays],
[SME/Coach], Title, Department
FROM [Test :  All Items]
UNION
SELECT [Cell], [Role-1], "" AS [Role-2], "" AS Remote, "" AS [On-Call], [POD], [BEMSID],
[A/P], [Full Name], Workday, [Work-Shift], [Holiday_Shift], [Holiday_Coverage], [Manager],
[Primary Phone], "" AS [Other Phone], SplitNGet([747-8 : All Items].[Holidays],";#",5) as [Holidays],
[SME/Coach], Title, Department
FROM [Test :  All Items]
UNION
SELECT [Cell], [Role-1], "" AS [Role-2], "" AS Remote, "" AS [On-Call], [POD], [BEMSID],
[A/P], [Full Name], Workday, [Work-Shift], [Holiday_Shift], [Holiday_Coverage], [Manager],
[Primary Phone], "" AS [Other Phone], SplitNGet([747-8 : All Items].[Holidays],";#",6) as [Holidays],
[SME/Coach], Title, Department
FROM [Test :  All Items]
UNION
SELECT [Cell], [Role-1], "" AS [Role-2], "" AS Remote, "" AS [On-Call], [POD], [BEMSID],
[A/P], [Full Name], Workday, [Work-Shift], [Holiday_Shift], [Holiday_Coverage], [Manager],
[Primary Phone], "" AS [Other Phone], SplitNGet([747-8 : All Items].[Holidays],";#",7) as [Holidays],
[SME/Coach], Title, Department
FROM [Test :  All Items] UNION SELECT [Cell], [Role-1], "" AS [Role-2], "" AS Remote, "" AS [On-Call], [POD], [BEMSID],
[A/P], [Full Name], Workday, [Work-Shift], [Holiday_Shift], [Holiday_Coverage], [Manager],
[Primary Phone], "" AS [Other Phone], SplitNGet([747-8 : All Items].[Holidays],";#",8) as [Holidays],
[SME/Coach], Title, Department
FROM [Test :  All Items] ;
 
0
 
Mark WillsTopic AdvisorCommented:
Not sure what splitNget function is, but guessing it splits up that holiday column...

Given contents of (say) : ";#11/27/09;#11/28/09;#11/29/09;#12/26/09;#12/27/09;#12/28/09;#12/29/09;#12/30/09;#12/31/09;#1/2/10;#1/3/10;#"

Then there is a trick you can use to unstring. It does require a table of numbers - from 1 through to max length of the holidays column - say a few hundred...

So, in your access database, go into new table (datasheet view) and in the empty table, change the first column name to N then in the first row type a 1 in that column, the next row, type 2 etc - it will start to guess that it is sequentially increasing and suggest the next number - so all you have to do is to hold down the down arrow key unti you have a few hundred values. Save that table as Numbers.

Your query is then really simple case of (fairly involved substrings) using MID and INSTR functions...

So, create a new query (use SQL mode), and paste the following code (changing MyHolidays to the correct table name, and [id] to the correct column name) :

SELECT [ID], datevalue(mid(holidays,n+2,instr(n+1,holidays,";")-(n+2))) as holiday_date
FROM MyHolidays, Numbers
WHERE LEN(TRIM(Holidays)) > 0
AND N <= LEN(TRIM(Holidays)) - 4
AND MID(holidays,n,1) = ";"
ORDER BY [ID],N

Run the query and see what it generates, and then save that query as Get_Holidays

Then all you have to do is to select * from get_holidays

Hope that helps....
0
 
Mark WillsTopic AdvisorCommented:
Have applied the above to a test database - open the Database window, go to Queries, and double click on Get_Holidays

It is not really needed for this question. Just the example "Holidays" string I posted above...
Test-holiday-dates.mdb
0
 
MikeTooleCommented:
I agree with Mark, a Numbers table is the easiest way to parse values from a string into separate rows.
0
 
u2babsAuthor Commented:
Thanks Guys
I knew it had to be simple, but with everything else I have to do,  a hed cold for two weeks and cough medicine I was lost in the fog!
Thanks so mcuh!
0
 
u2babsAuthor Commented:
Replacement database here.

Test-holidays.mdb
0
 
Mark WillsTopic AdvisorCommented:
*laughing* It wasn't THAT simple to come up with... Unless you have done that type of thing before.

Often a fresh set of eyes and a lateral approach seems to be simple, but it is similarly often that we do get bogged down from time to time and it can be very difficult to walk away from that (especially when it might appear to be so close). I think the lesson here is "sometimes the best way to progress is to stop".

Anyway, while I was kinda hoping to get the accepted answer, but grateful for the Assist.

Cheers,
Mark Wills
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

  • 6
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now