Working with linked (MySQL) data

M

Mike

I'm working with a datasource to obtain license information that's structed
like this:

serial#...swname1...swname2....swname3...
ABC.....ver#...........ver#...........ver#

I have no control over the structure and am trying to figure a way to query
the data to structure it the way I'd like (SWtitle, SWver, SWtotal).
Crosstab doesn't work, although I can do total queries for each software
title (selecting unique values). This *might* work but I'm trying to figure
out how to use a constant (the software name) as a new field value. I could
then query on that value (along with the version) to compare usage with
actual licenses.

Thanks in advance for any ideas!

Mike
 
J

John Spencer (MVP)

If you don't have too many columns with the name of the software, you can use a
union query to normalize the data.

SELECT "SWNAME1" as SWTitle, SWName1 as SWVer
FROM YourTable
UNION ALL
SELECT "SWNAME2" as SWTitle, SWName2 as SWVer
FROM YourTable
UNION ALL
SELECT "SWNAME3" as SWTitle, SWName3 as SWVer
FROM YourTable

You can save that query and use it as the source for a totals query. You may be
able to do this in one query as

SELECT SWTitle, SwVer, Count(SWTitle) as Count
FROM (
SELECT "SWNAME1" as SWTitle, SWName1 as SWVer
FROM YourTable
UNION ALL
SELECT "SWNAME2" as SWTitle, SWName2 as SWVer
FROM YourTable
UNION ALL
SELECT "SWNAME3" as SWTitle, SWName3 as SWVer
FROM YourTable
) as T
GROUP BY SWTitle, SWVer
 
M

Mike

This is so close to perfect! If I try to use the MySQL linked table,
however, Access complains (ODBC --call failed - then indicates a problem
with the select statement). Okay - so I'm not refering to the table
correctly but I'm wondering how to do that. I'm searching the MySQL site for
some ideas but so far coming up empty. Currently I'm getting around this by
copying the linked table using a make table query. If anyone knows the
syntax for this (or where I can find it), I'd appreciate it!

Thanks in advance,
Mike
 
M

Mike

I know it's been awhile, but I wanted to say THANK YOU VERY MUCH! This
worked like a charm *and* taught me something new about Access.

Mike
 
Top