Multiple Information in Columns

[Guest]

I have mutliple information in my columns for Access 2003.

Here is an example of what I have

Report # Worksheet Location Text
3055 A103 XYZ COmpnay
3055 A102 1231 Walnut St
3055 A104 Newark
3055 A105 NJ
3056 A103 ABC Company
3056 A102 456 Fultondale Ave
3056 A104 Twin City
3056 A105 MS

I am trying to run a query and pull the information out so that it reads

Name Address City State

When I try to run more than one at a time, I lose all of my records.
Eventually the goal is to put is all in a Report but I need to have the
information sepearted out before I can put it in a report.

What do i need to do?

Thanks,
Sally J

 

[Jeff Boyce]

Sally

Just to clarify, you have a table that lists BOTH a worksheet location AND
the text in that location? If so, why? That seems redundant.

Based on the example you gave, your table is storing different facts in the
same field (e.g., Company Name, Delivery Address, City, State). This may be
how you'd organize data in a spreadsheet, but it does not allow you to make
use of Access' relationally-oriented features and functions.

Consider checking into the topic of "normalization" before proceeding any
further...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

It was the way I received the information from another company. The worksheet
location is in regards to a report that I do. The information is vast and I
need to be able to make comparisions based off of the information which is
why I imported the information in to Access. Excel couldn't contain all of
the data points. Is there anything that I can do to fix this?

 

[Geoff]

It looks like you might be able to use the Concatenate code from Duane
Hookom.
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane

I've added it to my code, but for the life of me, can't get it to work.
Maybe you'll have more luck.

Geoff

 

[Guest]

Try this changing the table name from Sally_J to your table name ---
SELECT Sally_J.[Report #], IIf([Sally_J].[Worksheet
Location]="A103",[Sally_J].[Text],Null) AS Company,
IIf([Sally_J_1].[Worksheet Location]="A102",[Sally_J_1].[Text],Null) AS
Street, IIf([Sally_J_2].[Worksheet Location]="A104",[Sally_J_2].[Text],Null)
AS City, IIf([Sally_J_3].[Worksheet Location]="A105",[Sally_J_3].[Text],Null)
AS State
FROM ((Sally_J LEFT JOIN Sally_J AS Sally_J_1 ON Sally_J.[Report #] =
Sally_J_1.[Report #]) LEFT JOIN Sally_J AS Sally_J_2 ON Sally_J.[Report #] =
Sally_J_2.[Report #]) LEFT JOIN Sally_J AS Sally_J_3 ON Sally_J.[Report #] =
Sally_J_3.[Report #]
GROUP BY Sally_J.[Report #], IIf([Sally_J].[Worksheet
Location]="A103",[Sally_J].[Text],Null), IIf([Sally_J_1].[Worksheet
Location]="A102",[Sally_J_1].[Text],Null), IIf([Sally_J_2].[Worksheet
Location]="A104",[Sally_J_2].[Text],Null), IIf([Sally_J_3].[Worksheet
Location]="A105",[Sally_J_3].[Text],Null)
HAVING (((IIf([Sally_J].[Worksheet Location]="A103",[Sally_J].[Text],Null))
Is Not Null) AND ((IIf([Sally_J_1].[Worksheet
Location]="A102",[Sally_J_1].[Text],Null)) Is Not Null) AND
((IIf([Sally_J_2].[Worksheet Location]="A104",[Sally_J_2].[Text],Null)) Is
Not Null) AND ((IIf([Sally_J_3].[Worksheet
Location]="A105",[Sally_J_3].[Text],Null)) Is Not Null));

 

[John Spencer]

One method would be to use correlated subqueries to get the individual
items.

SELECT DISTINCT [Report#], Text as TheName
, (SELECT T2.Text FROM TheTable as T2 WHERE T2.[Report#] = T1.[Report#] and
T2.[WorkSheet Location] = "A102") as Street
, (SELECT T2.Text FROM TheTable as T2 WHERE T2.[Report#] = T1.[Report#] and
T2.[WorkSheet Location] = "A104") as City
, (SELECT T2.Text FROM TheTable as T2 WHERE T2.[Report#] = T1.[Report#] and
T2.[WorkSheet Location] = "A105") as State
FROM TheTable as T1
WHERE [WorkSheet Location] = "A103"


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Guest]

Sally,

Try this:
TRANSFORM First(Temp.Text) AS Text
SELECT Temp.[Report #]
FROM Temp
GROUP BY Temp.[Report #]
PIVOT Temp.[Worksheet Location];
(change the "Temp" table tto tthe name of your table)

This will give you a table like this:
Report # A102 A103 A104
A105
3055 1231 Walnut St XYZ COmpnay Newark NJ
3056 456 Fultondale Ave ABC Company Twin City MS
Then you can export this to another table, rename the columns and have data
you can use.