Transpose Rows to Columns in Access Query

Hello,

I am trying to transpose multiple columns into a specified column as shown 
below.

What it looks like:

1(Q1_A_1)  1(Q1_1-2)  1(Q1_A_3)  1(Q1_B_1)  1(Q1_B_2)  1(Q1_B_3) 
Low            High           -                1 Yr.           Now           
2 Yrs.

What it needs to look like: 

Risk     Readiness
Low     1 Yr.
High     Now
-          2 Yrs.

I am hoping there is a simple expression that I can type in the 'field' row 
of the query where I can just call out the fields I want to inlcude in that 
one column on seperate rows.  Also I am dealing with over 100 fields.  

Thanks,
Doug
0
Utf
3/13/2008 6:40:01 PM
access.queries 6343 articles. 1 followers. Follow

7 Replies
3187 Views

Similar Articles

[PageSpeed] 23

Sorry I forgot to mention that I will have various spreadsheets similar and 
more complex and the thought of building make tables and append queries is 
overwhelming. :-)  Any other feedback would be awesome!

"Compiling and Confused" wrote:

> Hello,
> 
> I am trying to transpose multiple columns into a specified column as shown 
> below.
> 
> What it looks like:
> 
> 1(Q1_A_1)  1(Q1_1-2)  1(Q1_A_3)  1(Q1_B_1)  1(Q1_B_2)  1(Q1_B_3) 
> Low            High           -                1 Yr.           Now           
> 2 Yrs.
> 
> What it needs to look like: 
> 
> Risk     Readiness
> Low     1 Yr.
> High     Now
> -          2 Yrs.
> 
> I am hoping there is a simple expression that I can type in the 'field' row 
> of the query where I can just call out the fields I want to inlcude in that 
> one column on seperate rows.  Also I am dealing with over 100 fields.  
> 
> Thanks,
> Doug
0
Utf
3/13/2008 8:31:01 PM
On Thu, 13 Mar 2008 11:40:01 -0700, Compiling and Confused
<CompilingandConfused@discussions.microsoft.com> wrote:

>Hello,
>
>I am trying to transpose multiple columns into a specified column as shown 
>below.
>
>What it looks like:
>
>1(Q1_A_1)  1(Q1_1-2)  1(Q1_A_3)  1(Q1_B_1)  1(Q1_B_2)  1(Q1_B_3) 
>Low            High           -                1 Yr.           Now           
>2 Yrs.
>
>What it needs to look like: 
>
>Risk     Readiness
>Low     1 Yr.
>High     Now
>-          2 Yrs.
>
>I am hoping there is a simple expression that I can type in the 'field' row 
>of the query where I can just call out the fields I want to inlcude in that 
>one column on seperate rows.  Also I am dealing with over 100 fields.  
>
>Thanks,
>Doug

I think word wrap made a real hash of this! I don't understand what it looks
like now. Could you repost in the syntax

Fieldname: Value
Fieldname: Value

Going from spreadsheet to relational database can be a real pain, since the
logical structure is so different. I'm GUESSING that your fieldnames encode
quarters and - what? years in the future? But what are the A and B?
-- 

             John W. Vinson [MVP]
0
John
3/13/2008 9:54:06 PM
Hi John,

The fields are results within a survey tool that I am using to gather 
succession information.  Even more confusing is the fields' naming schemes.  
I will try to simplify it for you below.

Column1 - Low
Column2 - Med
Column3 - High
Column4 - Now
Column5 - 1 Yr.
Column6 - 2 Yrs.
Column7 - 3 Yrs.

Need to look like this:

                               Risk             Readiness
Candidate 1              Low             Now
Candidate 2              Med             2 Yrs.
Candidate 3              High             3 Yrs.

I hope this helps.




"John W. Vinson" wrote:

> On Thu, 13 Mar 2008 11:40:01 -0700, Compiling and Confused
> <CompilingandConfused@discussions.microsoft.com> wrote:
> 
> >Hello,
> >
> >I am trying to transpose multiple columns into a specified column as shown 
> >below.
> >
> >What it looks like:
> >
> >1(Q1_A_1)  1(Q1_1-2)  1(Q1_A_3)  1(Q1_B_1)  1(Q1_B_2)  1(Q1_B_3) 
> >Low            High           -                1 Yr.           Now           
> >2 Yrs.
> >
> >What it needs to look like: 
> >
> >Risk     Readiness
> >Low     1 Yr.
> >High     Now
> >-          2 Yrs.
> >
> >I am hoping there is a simple expression that I can type in the 'field' row 
> >of the query where I can just call out the fields I want to inlcude in that 
> >one column on seperate rows.  Also I am dealing with over 100 fields.  
> >
> >Thanks,
> >Doug
> 
> I think word wrap made a real hash of this! I don't understand what it looks
> like now. Could you repost in the syntax
> 
> Fieldname: Value
> Fieldname: Value
> 
> Going from spreadsheet to relational database can be a real pain, since the
> logical structure is so different. I'm GUESSING that your fieldnames encode
> quarters and - what? years in the future? But what are the A and B?
> -- 
> 
>              John W. Vinson [MVP]
> 
0
Utf
3/13/2008 10:39:00 PM
On Thu, 13 Mar 2008 15:39:00 -0700, Compiling and Confused
<CompilingandConfused@discussions.microsoft.com> wrote:

>Hi John,
>
>The fields are results within a survey tool that I am using to gather 
>succession information.  Even more confusing is the fields' naming schemes.  
>I will try to simplify it for you below.
>
>Column1 - Low
>Column2 - Med
>Column3 - High
>Column4 - Now
>Column5 - 1 Yr.
>Column6 - 2 Yrs.
>Column7 - 3 Yrs.
>
>Need to look like this:
>
>                               Risk             Readiness
>Candidate 1              Low             Now
>Candidate 2              Med             2 Yrs.
>Candidate 3              High             3 Yrs.
>
>I hope this helps.

Well... that apparently oversimplified it. Where is the Candidate number field
in your data? You say you have over 100 fields (*very* spreadsheetish!); what
are some of the actual fieldnames in your Access table? How do you ascertain
from the seven rows above that Candidate1 has low risk and Now readiness?
-- 

             John W. Vinson [MVP]

0
John
3/14/2008 1:34:14 AM
Hi John,
When I import the data from excel into Access I keep the generic titles that 
are pulled from the survey because it is systematic.  The fields are named 
based on their location on the table question of the survey. 

Example: 1 (Q1_A_1) = Section 1, Question 1, column A, Row 1

This data is pulled from a table question.  Hence All data in row 1 of the 
table question is associated with candidate 1... row 2 is candidate 2.. and 
so forth.  Unfortunately I don't have the ease of pulling the suvey data with 
the same table question formatting as it is on the survey tool into Excel.  
Below is the data as it is pulled directly into a spreadsheet which I then 
import into an access database.

Column 1: 1 (Q1_A_1) - John Doe
Column 2: 1 (Q1_A_2) - Jane Smith
Column 3: 1 (Q1_A_3) - Ben Barker
Column 4: 1 (Q1_A_4) - Don Juan
Column 5: 1 (Q1_B_1) - Low
Column 6: 1 (Q1_B_2) - Med
Column 7: 1 (Q1_B_3) - Low
Column 8: 1 (Q1_B_4) - High
Column 9: 1 (Q1_C_1) - Now
Column 10: 1 (Q1_C_2) - 1 Yr.
Column 11: 1 (Q1_C_3) - 1 Yr.
Column 12: 1 (Q1_C_4) - 3 Yrs.

With the explanation above about how information for each candidate is 
ascertained, I would like for the data to look like this in the query (This 
is how the data looks in the table question):

Candidates        Risk          Readiness
John Doe           Low          Now
Jane Smith        Med          1 Yr.
Ben Barker        Low          1 Yr.
Don Juan           High         3 Yrs.

I hope this is a little clearer.


"John W. Vinson" wrote:

> On Thu, 13 Mar 2008 15:39:00 -0700, Compiling and Confused
> <CompilingandConfused@discussions.microsoft.com> wrote:
> 
> Well... that apparently oversimplified it. Where is the Candidate number field
> in your data? You say you have over 100 fields (*very* spreadsheetish!); what
> are some of the actual fieldnames in your Access table? How do you ascertain
> from the seven rows above that Candidate1 has low risk and Now readiness?
> -- 
> 
>              John W. Vinson [MVP]
0
Utf
3/14/2008 11:26:02 PM
On Fri, 14 Mar 2008 16:26:02 -0700, Compiling and Confused
<CompilingandConfused@discussions.microsoft.com> wrote:

>Hi John,
>When I import the data from excel into Access I keep the generic titles that 
>are pulled from the survey because it is systematic.  The fields are named 
>based on their location on the table question of the survey. 
>
>Example: 1 (Q1_A_1) = Section 1, Question 1, column A, Row 1
>
>This data is pulled from a table question.  Hence All data in row 1 of the 
>table question is associated with candidate 1... row 2 is candidate 2.. and 
>so forth.  Unfortunately I don't have the ease of pulling the suvey data with 
>the same table question formatting as it is on the survey tool into Excel.  
>Below is the data as it is pulled directly into a spreadsheet which I then 
>import into an access database.
>
>Column 1: 1 (Q1_A_1) - John Doe
>Column 2: 1 (Q1_A_2) - Jane Smith
>Column 3: 1 (Q1_A_3) - Ben Barker
>Column 4: 1 (Q1_A_4) - Don Juan
>Column 5: 1 (Q1_B_1) - Low
>Column 6: 1 (Q1_B_2) - Med
>Column 7: 1 (Q1_B_3) - Low
>Column 8: 1 (Q1_B_4) - High
>Column 9: 1 (Q1_C_1) - Now
>Column 10: 1 (Q1_C_2) - 1 Yr.
>Column 11: 1 (Q1_C_3) - 1 Yr.
>Column 12: 1 (Q1_C_4) - 3 Yrs.
>
>With the explanation above about how information for each candidate is 
>ascertained, I would like for the data to look like this in the query (This 
>is how the data looks in the table question):
>
>Candidates        Risk          Readiness
>John Doe           Low          Now
>Jane Smith        Med          1 Yr.
>Ben Barker        Low          1 Yr.
>Don Juan           High         3 Yrs.
>
>I hope this is a little clearer.

Much. That's got to be the wierdest layout for questionnaires that I've ever
seen. I presume that one time there might be four rows, the next time eleven?

If it's JUST as you describe a Normalizing Union query will do the job. I'll
assume that the first row of the spreadsheet has your Q names and that you
have used them as the fieldnames for a (linked or imported, it shouldn't
matter) temporary table, which I'll call tblTemp.

You will need to go into the SQL window of a new Query, and edit or copy and
paste:

SELECT [Q1_A_1] AS Candidate, [Q1_B_1] AS Risk, [Q1_C_1] AS Readiness
FROM tblTemp
WHERE [Q1_A_1] IS NOT NULL
UNION ALL
SELECT [Q1_A_2] AS Candidate, [Q1_B_2] AS Risk, [Q1_C_2] AS Readiness
FROM tblTemp
WHERE [Q1_A_2] IS NOT NULL
UNION ALL
SELECT [Q1_A_3] AS Candidate, [Q1_B_3] AS Risk, [Q1_C_3] AS Readiness
FROM tblTemp
WHERE [Q1_A_3] IS NOT NULL
UNION ALL
SELECT [Q1_A_4] AS Candidate, [Q1_B_4] AS Risk, [Q1_C_4] AS Readiness
FROM tblTemp
WHERE [Q1_A_4] IS NOT NULL


This query will "unravel" the spreadsheet into a tall-thin table. I'm sure you
can extend the logic to more columns as needed.

You can then base a MakeTable or (better) Append query - into a predefined
normalized empty table - on this stored UNION query. Running it will pull the
data out of the spreadsheet into the new table.
-- 

             John W. Vinson [MVP]
0
John
3/15/2008 4:58:12 AM
John, thanks for you help, I got the just of the coding that I needed and 
actually was able to make up the rest to get it to do what I needed it to.  I 
really appreciate your feedback.  This is an attempt of our company to use a 
survey as an interim tool to gather succession data before we implement ERP.  
Yes it's got to be the worst format we can get information in and requires 
customized surveys for each position identified to succession planning.  But 
it is pretty on the front end, which is what matters to our execs.

This was my first time writing a code for a union query and I believe this 
will help my overall process of translating survey succession data into a 
meaningful report in Access.  Thanks again!

"John W. Vinson" wrote:

> On Fri, 14 Mar 2008 16:26:02 -0700, Compiling and Confused
> <CompilingandConfused@discussions.microsoft.com> wrote:
> 
> >Hi John,
> >When I import the data from excel into Access I keep the generic titles that 
> >are pulled from the survey because it is systematic.  The fields are named 
> >based on their location on the table question of the survey. 
> >
> >Example: 1 (Q1_A_1) = Section 1, Question 1, column A, Row 1
> >
> >This data is pulled from a table question.  Hence All data in row 1 of the 
> >table question is associated with candidate 1... row 2 is candidate 2.. and 
> >so forth.  Unfortunately I don't have the ease of pulling the suvey data with 
> >the same table question formatting as it is on the survey tool into Excel.  
> >Below is the data as it is pulled directly into a spreadsheet which I then 
> >import into an access database.
> >
> >Column 1: 1 (Q1_A_1) - John Doe
> >Column 2: 1 (Q1_A_2) - Jane Smith
> >Column 3: 1 (Q1_A_3) - Ben Barker
> >Column 4: 1 (Q1_A_4) - Don Juan
> >Column 5: 1 (Q1_B_1) - Low
> >Column 6: 1 (Q1_B_2) - Med
> >Column 7: 1 (Q1_B_3) - Low
> >Column 8: 1 (Q1_B_4) - High
> >Column 9: 1 (Q1_C_1) - Now
> >Column 10: 1 (Q1_C_2) - 1 Yr.
> >Column 11: 1 (Q1_C_3) - 1 Yr.
> >Column 12: 1 (Q1_C_4) - 3 Yrs.
> >
> >With the explanation above about how information for each candidate is 
> >ascertained, I would like for the data to look like this in the query (This 
> >is how the data looks in the table question):
> >
> >Candidates        Risk          Readiness
> >John Doe           Low          Now
> >Jane Smith        Med          1 Yr.
> >Ben Barker        Low          1 Yr.
> >Don Juan           High         3 Yrs.
> >
> >I hope this is a little clearer.
> 
> Much. That's got to be the wierdest layout for questionnaires that I've ever
> seen. I presume that one time there might be four rows, the next time eleven?
> 
> If it's JUST as you describe a Normalizing Union query will do the job. I'll
> assume that the first row of the spreadsheet has your Q names and that you
> have used them as the fieldnames for a (linked or imported, it shouldn't
> matter) temporary table, which I'll call tblTemp.
> 
> You will need to go into the SQL window of a new Query, and edit or copy and
> paste:
> 
> SELECT [Q1_A_1] AS Candidate, [Q1_B_1] AS Risk, [Q1_C_1] AS Readiness
> FROM tblTemp
> WHERE [Q1_A_1] IS NOT NULL
> UNION ALL
> SELECT [Q1_A_2] AS Candidate, [Q1_B_2] AS Risk, [Q1_C_2] AS Readiness
> FROM tblTemp
> WHERE [Q1_A_2] IS NOT NULL
> UNION ALL
> SELECT [Q1_A_3] AS Candidate, [Q1_B_3] AS Risk, [Q1_C_3] AS Readiness
> FROM tblTemp
> WHERE [Q1_A_3] IS NOT NULL
> UNION ALL
> SELECT [Q1_A_4] AS Candidate, [Q1_B_4] AS Risk, [Q1_C_4] AS Readiness
> FROM tblTemp
> WHERE [Q1_A_4] IS NOT NULL
> 
> 
> This query will "unravel" the spreadsheet into a tall-thin table. I'm sure you
> can extend the logic to more columns as needed.
> 
> You can then base a MakeTable or (better) Append query - into a predefined
> normalized empty table - on this stored UNION query. Running it will pull the
> data out of the spreadsheet into the new table.
> -- 
> 
>              John W. Vinson [MVP]
> 
0
Utf
3/18/2008 11:38:00 PM
Reply:

Similar Artilces:

double clicking and draging a column in a chart to chg data
in Excel 2003, double clicking on a column in a chart and then dragging the column up or down would change the data in a table upon which the chart depended. How does one do this in excel 2007? Tom Hi, That feature has been removed in 2007, there is no way to do it. If this helps, click the Yes button. -- Thanks, Shane Devenshire "Tom of inns" wrote: > in Excel 2003, double clicking on a column in a chart and then dragging the > column up or down would change the data in a table upon which the chart > depended. > > How does one do this in excel 2007? &g...

Business Portal Error-SQL server does not exist or access denied
Hi, We are running business portal 4.0 for one of our customer. It was running correctly, however, they have changed the SQL server port (previously it was set as default 1433). After that the business portal becomes very slow and while creating a new request (purchase requisition) if we open the item pop up; it is showing exception "SQL Server does not exist or access denied...." Can any body tell me how can I provide the new port number to business portal connection to the database. Thanks and Regards, Waliullah, Thanks for using the newsgroups. I have a...

What's happened to the findfirst function in Access 2010?
I have a working Access 2007 Application which is now failing miserably in 2010. It hinges on location the first available working document in a table runing the following VBA code: With Me.Recordset .FindFirst "([fld1] + [fld2]) = 0" If .NoMatch Then .FindLast "([fdl1] + [fld2]) <> 0" Exit Sub End If .... FURTHER PROCESSING ... What must I do to correct this? Go through a record by record search? End With In 2007, this works correctly, stopping at the appropriate record (approc. rec 1385 in the recordset...

transfer data from multiple columns to singlr column
I have data in form a d g b e h c f i (but larger scale) and I need it in a single column going a to z. Hi, highlight you data, copy, go to the column where you want to see the data, paste special, transpose "lc85" wrote: > I have data in form a d g > b e h > c f i (but larger scale) > and I need it in a single column going a to z. You up for using a macro? Sub ToOneColumn() 'dantuck Mar 7, 2007 &...

open two different Access reports
Hi, I was wondering if i can get some help here. I have two different reports that i want to open when a user clicks a button to view the reports for printing. Is there any way of popping them up at the same time in VBA? Thank you in advance Associates wrote: >Hi, > >I was wondering if i can get some help here. I have two different reports >that i want to open when a user clicks a button to view the reports for >printing. Is there any way of popping them up at the same time in VBA? > >Thank you in advance Yes. Call the DoCmd.OpenReport command twic...

update column
How would I update a column with numeric values so that there are 3 leading zeros for each row? hi it is not possible to add leading zeros to a numeric value. Mathematically, this is redundent and unnecessary. "brian" wrote: > How would I update a column with numeric values so that there are 3 leading > zeros for each row? opps. hit the post button too quick. option 1. custom format if your numeric value is 12345 then see the custom format to 00000000. note. format do not change data - it just changes the way it looks in the cell. option2. format to text then use the c...

Summing Alternate Rows
We have spreadsheets with many rows and need to sum alternate rows. Is there a way to do this without entering a formula using the number of each row? -- KarenQ ------------------------------------------------------------------------ KarenQ's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=19608 View this thread: http://www.excelforum.com/showthread.php?threadid=395115 Try this: =SUMPRODUCT(--(MOD(ROW($A$1:$A$18),2)=1),$A$1:$A$18) That formula sums odd-row values in the range A1:A18. To sum even-row values the formula is: =SUMPRODUCT(--(MOD(ROW($A$1:$A$18...

Access 2007 and Vista code problem
I am developing a database on a XP SP2 machine. On a form I have created some buttons with event procedures that run 2 queries and open a form. I sent the db to someone running Access 2007 on a Vista machine and nothing happens when she clicks the buttons and there are no messages. I know nothing about Vista so thanks in advance for any help. Tim Might be a reference problem, have them open the open a code window and click tools references. See what says missing and tell them what should be there, Add it and it should work. Duff "Tim Reid" <TimReid@discussions.micr...

Web query timeout setting.
Dear Group, I fill an Excel table using data that I take from an Internet site. Unfortunately, this site is very slow and so I often get a "query did not provide any data" error message. How can I increment the default web query timeout limit? Thanks in advance, Enrico. ...

Global Column Row Preview Font Size
I know I can change the column, row and preview font size for the current email folder's view, but how do I do it for all of the email folders? I have loads of email addresses each with lots of folders. I don't want to have to do each one at a time. Surely there must be a default font setting (even if it's only in the registry)? Thanks in advance, Tim. I too would love an answer to this. Ian "Timie Milie" <tim_milstead@yahoo.co.uk> wrote in message news:45782ee8$0$27107$db0fefd9@news.zen.co.uk... >I know I can change the column, row and preview font ...

Using Access 2003 on client to see Sharepoint 2007
Testing a Sharepoint environment and I can easily manipulate and run the database from Access 2007, but when I try to use Access 2003 it wants me to save the database to a location. The Access 2003 has the compatiblity pack, but it seems to not work when I download it. So the question is using Sharepoint 2007 and posting the Access 2007 format to it, can a user go to the sharepoint site and use a 2003 Access database that has the compatibility pack and open without having to download it? -- Message posted via http://www.accessmonster.com Send check for $500.00 to: David A Jenn...

compact database in VBA access 2007
Hello, I have recently upgrade to office 2007 from office 2003. To compact a database from within the database itself, I used the follwing code. Unfortunately it no longer works in access 2007. Is there some similar code that will work? Public Function FncCompactTheCurrentDB() CommandBars("Menu Bar"). _ Controls("Tools"). _ Controls("Database utilities"). _ Controls("Compact and repair database..."). _ accDoDefaultAction End Function Thank You, SL On Thu, 28 Jan 2010 17:34:01 -0800, SL <SL@discussions.microsoft....

How do I insert space between 2 consecutive columns of an XL Shee.
I want to have space between two consecutive columns of a worksheet (of course, without having inserted another column between the two) in order to have separated the Border Lines of the adjacent cells/columns. Please guide me if it can be done in XL. Can you achieve the effect that you're looking for by using a double vertical border down the right side of the left column and having no border down the left side of the right column? Rgds, ScottO "Shamshad Butt" <Shamshad Butt@discussions.microsoft.com> wrote in message news:1222EE13-11A9-4354-9F12-D1F1155D3902@microsof...

LDAP Write access?
My ldap server allows Write access to entries - and a few clients now support this. Any plugins available for Outlook to allow this too? Thanks None that I'm aware of. -- Diane Poremsky [MVP - Outlook] Author, Teach Yourself Outlook 2003 in 24 Hours Need Help with Common Tasks? http://www.outlook-tips.net/beginner/ Outlook 2007: http://www.slipstick.com/outlook/ol2007/ Outlook Tips by email: dailytips-subscribe-request@lists.outlooktips.net Subscribe to Exchange Messaging Outlook newsletter: EMO-NEWSLETTER-SUBSCRIBE-REQUEST@PEACH.EASE.LSOFT.COM Outlook Tips: http://www.outlook...

MS Access 2007 code not running
I have created a brand new database in Access 2007 and linked some VB code to run on Form_Load. This code worked perfect with no problems the day I created it. Now today I opened the same form and it is not running the Form_Load code anymore (and yes it is still there, I did not delete it). I got frustrated so I copied and pasted the code to Form_Current and several On_Change events and set breakpoints at the beginning of all the events just to see if it was the Form_Load that was not triggering, but NO events are triggering my VB code. Any ideas? See this page: http://accessjunkie.com/fa...

unable to grant existing user access to TEST company
Set up test company using live company data; ran the script referenced in CustomerSource article ID#871973; can grant and remove user access in all other companies; ran grantsql 9.2; the dexsql log shows the user already has access probably because of using the other company data for the test; I can set up a new user and grant them access, existing users who already have access can view the company...but, I cannot figure out how to grant access to the TEST company to an existing user. The error popup reads "The user could not be added to one or more databases." Dynamics 9.0,...

Adding blank "separator" Rows in a Pivot Table
Does anyone know how to insert blank rows into a pivot table (say, between groupings, etc.)? There has to be a way, since some of the various table formats that come with Excel include blank rows. Double-click the field button that you want to format Click Layout Add a checkmark to Insert blank line after each item Click OK, twice Bucko wrote: > Does anyone know how to insert blank rows into a pivot table (say, > between groupings, etc.)? There has to be a way, since some of the > various table formats that come with Excel include blank rows. -- Debra Dalgleish Contextures http...

Accessing another user's calendar OL2000/OL2003/Ex2000
Here's the situation: Running Exchange2000, v 6.0, sp4. Users are on XP/OL2003 or NT/OL2000. (we're in the process of upgrading all of the workstations to XP.) VIP user is on XP/OL2003. He can access his calendar just fine on his workstation. His secretary and other office admin type people are still on NT/OL2000. They have reviewer or editor rights to his calendar. They try to access his calendar by chosing File/Open/Other User's Folder... and put in his name and calendar. When they do this, the process hangs. In Task Manager, Outlook is "Running." End the Outloo...

Determine a result of one column based on conditions in two column
Example Col A Col B Count the number of a's in Col B only when an x is in Col A x a x a Result should be 2 y a z p I can't figure it out x t x m Thanks try this =SUMPRODUCT(--(A2:A7="x"),--(B2:B7="a")) -- Hope this help Please click the Yes button below if this post have helped answer your needs Thank You cheers, francis "tel703" wrote: > Example > Col A Col B Count the number of a...

SQL query / Formula for Reorder Point?
Is it possible to issue an SQL command(s) that would: 1. Set the Restock Level for all items to be equal to the current on-hand quantity for each item. 2. Set the Reorder point for all items to be equal to 1/3 of that items Restock Level. I'm currently doing this manually, or using a formula in excel when items are initially imported into the DB, but there are still several thousand that need to be retro-fitted like this... Thanx in advance! -=RFM=- UPDATE Item SET RestockLevel=(Quantity - QuantityCommitted) UPDATE Item SET ReorderPoint=ROUND(RestockLevel/3,0) - Evan Culver Ne...

How to place a stable title row?
Hi there! For a scientific analyse I need to create an Excel table with more than 1.000 rows content. Now, how can I create a title row that doesn't scroll with the rest of the content, so one that is always visible at the top, while you scroll up and down the rest of the content. I hope that's possible like this! Otherwise, it will be pretty difficult to enter the data into the correct (of about 15) column. Thanks for your help in advance! One way: Assuming headers are in row1, Select A2. Click Window > Freeze pane This freezes the 1st row, so it always remains in view w...

Column searching problem
I have a worksheet called "net" containing the following: NETWORK AVG MIN MAX STD SAMPLES ABC 17.17 16.26 17.71 0.4469 19 CBS 12.99 11.69 14.56 0.6524 30 NBC 15.39 14.08 16.70 0.7323 38 NBC* 15.12 14.08 15.99 0.4910 31 Fox 10.63 9.07 12.09 0.9374 8 HBO 10.94 9.07 13.22 1.2507 19 Showtime 11.52 6.84 13.23 1.9017 11 HDNet 18.14 14.22 18.85 1.1619 14 HDNet* 18.45 18.13 18.85 0.2847 13 HDNet-Movies 1...

Column help
I have a seating chart I am trying to design for our graduation ceremony. Right now I have the report set up using columns. I have a rectange box with the graduates first, middle and last name in the box. I need 16 columns across the page for the seats which I have working. My problems are that I need a bold line going down the middle to divide the 8th and 9th rows. ( Students come in from 2 lines and meet in the middle of each row.) My next problem is that I need to have another column to the left of the 1st column and one to the right of the 16th column that will count the row num...

How can I sum only amounts that are in BOLD format within a column
Hi! I need to get a total from a worksheet that has hundreds of amounts in it. However, I only need the total of the amounts that were marked with have BOLD font. Please help me I don't have much experience with EXCEL. Thank you very much! W a n d a try this for column D Sub sumbold() x = Cells(Rows.Count, "d").End(xlUp).Row For Each c In Range(Cells(2, 4), Cells(x, 4)) If c.Font.Bold Then mysum = mysum + c Next MsgBox mysum End Sub -- Don Guillett SalesAid Software donaldb@281.com "Wanda" <Wanda@discussions.microsoft.com> wrote in message news:89A268A6-...

Can't use openURL or access internet on some locations
I have previously raised a question where openUrl threw an exception in a specific office location. However the problem I have now is that the application just freeze when I call openURL. It seems as it is waiting for something. This only happens at one company so far the company does have a proxy but so do I at work and I have no problems. I have also tried the Microsoft TEAR sample and it behaves the exact same way, it says "Opening internet...Connection made" and then it just stops. If I run it on my computer it gets the webpage and everything finishes ok. I have tried to set t...