Importing Crosstab Data from Excel

[Guest]

I am importing data from an Excel spreadsheet into an Access table . The
spreadsheet is in Crosstab form. The columns are Year,Location, and then
columns for each month with sales data in each cell.

I need to query this data with Location and Month as the parameters, without
having to write a seprerate select statement for each month(column),
essentially I need to convert the crosstab data into a list that can be
queried based on Location and Month. Is there a way to do this?

 

[MGFoster]

I am importing data from an Excel spreadsheet into an Access table . The
spreadsheet is in Crosstab form. The columns are Year,Location, and then
columns for each month with sales data in each cell.

I need to query this data with Location and Month as the parameters, without
having to write a seprerate select statement for each month(column),
essentially I need to convert the crosstab data into a list that can be
queried based on Location and Month. Is there a way to do this?

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

Yes, use a UNION query (as a View) to "normalize" the s/s:

SELECT [Year], Location, 1 AS Month, JanuaryValue As theValue
FROM <Excel s/s table name>
WHERE JanuaryValue IS NOT NULL

UNION

SELECT [Year], Location, 2 AS Month, FebruaryValue
FROM <Excel s/s table name>
WHERE FebruaryValue IS NOT NULL

UNION

.... etc.

Then query the Union query to get the data you want:

PARAMETERS [Which month?] Byte, [Which Location?] Text;
SELECT Location, theValue
FROM <the UNION query name>
WHERE Month = [Which month?]
AND Location = [Which Location?]

Change the Location's data type to its true data type.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

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

iQA/AwUBRFJKzoechKqOuFEgEQKVNgCggtiaW+p8BliqG0cgNi+gmi3aPLsAn0v8
4J8cSBTxOzC4Y3eZZHoGYkK+
=iWjK
-----END PGP SIGNATURE-----