Multiple Information in Columns

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
0
Utf
8/22/2007 6:06:09 PM
access.queries 6343 articles. 1 followers. Follow

5 Replies
1184 Views

Similar Articles

[PageSpeed] 38

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


"Sally J" <SallyJ@discussions.microsoft.com> wrote in message 
news:5C53861A-A1EE-4836-9F5D-107662E0E901@microsoft.com...
>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 


0
Jeff
8/22/2007 6:13:20 PM
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?

"Jeff Boyce" wrote:

> 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
> 
> 
> "Sally J" <SallyJ@discussions.microsoft.com> wrote in message 
> news:5C53861A-A1EE-4836-9F5D-107662E0E901@microsoft.com...
> >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 
> 
> 
> 
0
Utf
8/22/2007 6:26:03 PM
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

"Sally J" <SallyJ@discussions.microsoft.com> wrote in message 
news:FBFA1EFF-E5A1-4FAD-B410-CEC86BE6A37C@microsoft.com...
> 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?
>
> "Jeff Boyce" wrote:
>
>> 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
>>
>>
>> "Sally J" <SallyJ@discussions.microsoft.com> wrote in message
>> news:5C53861A-A1EE-4836-9F5D-107662E0E901@microsoft.com...
>> >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
>>
>>
>> 


0
Geoff
8/22/2007 7:02:46 PM
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));

-- 
KARL DEWEY
Build a little - Test a little


"Sally J" wrote:

> 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
0
Utf
8/22/2007 7:38:06 PM
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
..

"Sally J" <SallyJ@discussions.microsoft.com> wrote in message 
news:5C53861A-A1EE-4836-9F5D-107662E0E901@microsoft.com...
>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 


0
John
8/22/2007 7:39:08 PM
Reply:

Similar Artilces:

Totalling Columns
I have two columns of figures B2:B22 and C2:C22. I have merged cells B23 and C23 and want to have figure Column C minus Column B showing in this cell. No matter what form I take I can't achieve this. Can anyone help, please. TIA Ed Hi Ed: In the merged cell {B23-C23} enter the formula: =SUM(B2:B22)-SUM(C2:C22) -- Gary's Student "Ed O'Brien" wrote: > I have two columns of figures B2:B22 and C2:C22. I have merged cells B23 and > C23 and want to have figure Column C minus Column B showing in this cell. > > No matter what form I take I can't...

Code to fill Word form for multiple records from Access
This is the code I've been working with, but it freezes Access: Private Sub Command66_Click() 'Print Physician Profile. Dim appWord As Word.Application Dim doc As Word.Document 'Avoid error 429, when Word isn't open. On Error Resume Next Err.Clear 'Set appWord object variable to running instance of Word. Set appWord = GetObject(, "Word.Application") If Err.Number <> 0 Then 'If Word isn't open, create a new instance of Word. Set appWord = New Word.Application End If Set rs = Db.OpenRecordset rst.Open Me.RecordSource, CurrentProject.Co...

Graphing Multiple Data Points
What would be the best way to graph this and how? I've been trying to do a line graph because this is how we wanted it displayed but I'm having a hard time trying to figure this out. Sample Data Monday Tuesday Wednesday Thursday Friday Saturday Sunday Week 1 Occupancy 56% 65% 54% 75% 80% 82% 54% ADR $80.00 $85.00 $84.50 $86.50 $89.50 $84.26 $86.54 Week 2 Occupancy ADR Week 3 Occupany ADR Week 4 Occupancy ADR T...

Public folders w/ multiple addresses
I have a mail-enabled public folder with multiple email addresses, and I need to be able to determine through what email address was each email received. The only way I currently know of is to display "Options" on the message and see that in the "Internet Headers". Off course this can be workarounded by creating separate public folder for each address and then copy the emails to the target folder, but there must be some better solution to this. Thanks, George ...

Multiple-Test Conditional Formatting
Hi, everyone! I have a spreadsheet in which one column (B) contains an item "type", e.g. "C" or "H". In another column (G), I put a "required" sub-item. I am trying to conditionally format the cell in column G to do the following: If Col B = "C" and Col G is blank, make background of Col G red; Otherwise, make background of Col G normal. Using normal CF, I can make G's background red if Col B = "C" (formula is), or I can make it red if Col G is blank (cell value is), but can't seem to find the trick of combinin...

Multiple reports in same Document
I am looking for a way to show on the same document, two different type of data as follows: I have a data table with any number of columns that are set in width. I am wanting to insert a pivot table report imediately below the dat table. The pivot table picks up the column width from the data tabl already in the sheet. This causes the pivot table fields to be eithe too large or too small. If I correct the pivot table, then the dat table attributes are changed. Thanks -- JGallik0 ----------------------------------------------------------------------- JGallik01's Profile: http://www...

Multiple Hyperlinks in 1 cell?
I have some spreadsheets that have a column with some text (4 numbers) that link to a webpage, where the URL ends with the four numbers (eg.1234). Sometimes I place an additional 4-number reference to another Webpage in the same cell, so it looks like (eg.1234/5678). I have to get rid of the link since I only know how to set it for one Webpage or the other. Is there a way for users to click 1234 and go to that page, or click 5678 (in the same cell) and go to that selected page? Thanks! Not that I'm aware of. "davidemile" <davidemile@hotmail.com> wrote in message news:...

Multiple windows updating multiple rows simultaneously ?
I'm maintaining an application that currently allows entry of new records or updating exisiting records. What would be involved in modifiying the application to allow multiple windows to be open simultaneously. In other words, if the user is updating or creating a record in one window, he can leave that window and go update another record in another window or create a new record in another window, returning to the other windows afterwards. How complex would this be to handle? Thanks in advance for any help. Yes, I guess that's what it would amount to. "SteveM" wrote...

Vlookup on multiple sheets
I am copying a database fron access into excel; due to the size (100000+ lines) i have to put it on two excel sheets. i will like to do a vlookup that will search both sheets for the result i am looking for. can someone please help You need something like: =IF(ISNA(VLOOKUP($A1,Sheet1!A:F,2,0)),IF(ISNA(VLOOKUP($A1,Sheet2!A:F, 2,0)),"not present",VLOOKUP($A1,Sheet2!A:F,2,0)),VLOOKUP($A1,Sheet1! A:F,2,0)) Looks in Sheet1 first for an exact match on A1 in column A, and if not found then looks in Sheet2 for an exact match. If A1 is not in either sheet, returns "not present"....

copy only unhide columns
Dear Sir/Madam, I need to how do I copy all values in a worksheet EXCEPT hidden column and paste them into a new workbook. Regards, sea -- Message posted from http://www.ExcelForum.com Sean, Select your columns even the hidden ones. then press F5 click on "special" then click on visible cells only then do your copy and pasting. Regards, Cesar zapata seanyeap < wrote: > Dear Sir/Madam, > I need to how do I copy all values in a worksheet EXCEPT hidden columns > and paste them into a new workbook. > > Regards, > sean > > > --- > Message ...

Last Value in a Column when Value <> 0
Hi, can someone provide me a formula that populates a cell with the last value in a column that does not = 0? Below is an example 3 6 7 0 0 0 The goal is to populate a cell with the value of 7. I am currently using the below formula that populates the last value of a column: =INDEX('Retirement Total'!B:B,MATCH(9.99999999999999E+307,'Retirement Total'!B:B)) I do not however know how to change this to not populate the last value when it is zero. Your help is appreciated. Assuming you want the last *numeric* value that <>0. Try something like this: =LOOKUP(1E100,1/'...

group by column
hi all, I've got a file like this... A Number State 1 02 A 1 02 F 1 02 S 1 04 A 1 04 C ..... .... .... .... I'd like to create a new sheet in the same file with only 1 row for every number The state is decided in this order A R F C S That means that if I've got the same number but different state I should keep the row with the highest order. Any hints?? thanks Rossella This uses Pivot Table. Assume your data looks like this: 02 A 01 F 01 S 01 R 02 C 03 F 01 R 02 A 03 S 03 C Pivot Table > Multiple Cons...

Copying Part of a row down part of a column
Hi, My problem is this. I have a specific row of data that I need to copy part of down a column, until the next row of data. The data is in a format 12345 abcd efgh ijkl I only need to copy the numeric portion (12345) down the column, until the next row I need to copy (same format) shows up. What's the best/quickest way to do this. It's like an =IF function, but I'm not sure of the syntax. Thanks Try this: assuming your column A is the column with your data with blank rows i between and column B is where you want the results then. =IF(A2<>"",LEFT...

dlookup with multiple criteria
I have a form that needs to look up a "goal" by matching several fields in a table. I can't figure out how to do dlookup with multiple criteria frmManualTaskDataEntry [employee] [date] [mailcode] [state] [disabilityind] [volumecode] tblMailCodeTasks mailcode state disabilityind state goal Buzzmcduffie - You use AND to connect the multiple criteria, and must include proper delimeters for text and date fields. It will look something like this (untested): DLookup("[goal]","[tblMailCodeTasks]","[mailcode] = '" &...

The most repeted item in a column?
How to get the highest number repeted in a column which exist of numbers? Use MODE(column). Hope this helps. Pete On Dec 3, 9:25=A0pm, kaveh <kavehamo...@yahoo.ca> wrote: > How to get the highest number repeted in a column which exist of numbers? ...

Import multiple photos into excel
I am trying to create a photo sheet that will allow me to import all photos in a directory into an excel spreadsheet. I want it to: Import all images in a directory. (even if I have to choose to open the folder and then select all) Place each photo into a separate box Auto-format all the photos to the same size where (2) images fit on each printed page. Have a space for a text description to the right of each photo. Auto-number each row/photo if possible (so it changes all the numbers if I delete a row) Place a header with a logo on each printed page. Insert a merge field in t...

Stacked Column gap width
I have a stacked column graph and I have set the gap width to 0. There is, however, still space between the stacked columns. The gap looks to be about twice as wide as the column themselves. Any idea why? The column overlap is set to 100. The Gap Width is set to 0. In case it matters, the X axis is a time scale, all axis settings are automatic. It looks like both ClusteredColumn and StackedColumn graph types have some intra-cluster spacing that cannot be eliminated... Brian Herbert Withun When you use a time-scale axis, it creates categories for each day (all days). The time-scale is ...

Multiple selection in the page area of a pivot table
Is there any way i can have multiple selection check boxes in the page area of a pivot table? ...

Move column to in-between existing columns
Is there a way to move an entire column to in-between existing columns without getting the error message, "Do you want to replace contents in destination cells?" and also without VB? I'm no use with coding... What I've been doing is to insert new blank column, highlight and drag my existing column to this new column, then delete the old column. Have you tried "control x" to cut the column from the old location and then ctl v to paste it to the new? Peter London, UK "Melissa" wrote: > Is there a way to move an entire column to in-between exis...

Printing columns
This is a multi-part message in MIME format. ------=_NextPart_000_0030_01C77B57.C072D1F0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable All, Please forgive this very simple task request. I am trying to eliminate = the white space on the right side of a printed page and ultimately the = total number of pages. All of that means that I would like to display = and print multiple groups of columns within the same page. Example, the spreadsheet contains three columns of information: Column 1 is 2 characters Column 2 is 12 characters Co...

Sequential numbering when X column repeats records
I have a table with 2 columns Column 1 has records A A A B B C A Column 2 needs to have the number of times row one repeats a record. Something like this 1 2 3 1 2 1 Any formula that will find records on column one and sequentially number column B depending on amount of records? Thank you -RoMo -- RoMo robpiolink, Give each record a value of one with a calculated bound field in your query... GrpCounter : 1 Add GrpCounter to the report section. Group your report on each letter (in this example), and set GrpCount Running S...

Need to Sum one column for each row that is part of a group and then filter out dups
I have around 53,000 rows of data with columns A-I. The first five rows for example have data for a particular student... STUDENT A. Rows 6 through 15 have data about another student... STUDENT B, and so forth. Each column per student has the same data in it with the exception of the last column, column I. Column I has the students GPA (Grade Point Averages) in it per row. So, for example, STUDENT A might have a GPA in row 1 of 4.0, a GPA in row 2 of 2.5, a GPA in row 3 of 4.0, a GPA in row 4 of 3.0, and a GPA in row 5 of 4.0. Since all data in the rows for each person has the same...

Some Cells display text others dates in the same column
I imported a .csv file into Excel. One column contains the SKU # of a product. All my SKU's are formatted as x-xxxx. However some of the cells in the column display the SKU as it should, but many (45 out of 170) are displayed as a date format mmm-yy. Each one of the 45 is different. The cells in question show up as "custom" and when I try to change it to "text" I get a completey different number than the SKU I need. Somebody please help. Hi Phil, Format the entire column manually, choose Format, Cells, Number tab, Custom and enter #-#### in the Type box...

Rows and Columns italicized
the A,B,C and 1,2,3 are all italicized. How can I change it back? This is also true on all other apps not just Excel but since that's where it started I thought I'd post it here. What font do you have under tools>options>general, excel's default is arial? Also check if you have any styles applied under format>style Regards, Peo Sjoblom "bradsdad25" <bradsdad25@discussions.microsoft.com> wrote in message news:DC440A19-14E2-4DBF-91B4-230DAFE4D7A8@microsoft.com... > the A,B,C and 1,2,3 are all italicized. How can I change it back? This is > also tr...

Multiple IF Scenario
Here is my scenario (I am an average user and do not make complicate formulas): I am pulling a single digit from a 12 digit code using (MID(B11,4,1)) Example: B11 = XXXAXXXXXXXX, then the digit pulled is "A" If the 4th digit is 1-9, then nothing needs to be done, but, if the 4t digit is A, B or C, then A=10, B=11, C=12 As you might have guessed, the 4th digit is a month code, that I nee to extract and properly display as a number ranging from 1-12 I tried a multiple IF formula that failed to produce a value, but di not cause errors. I tried AND, OR, THEN, ELSE, but I am not very ...