multipule columns as on column

I need to create a make-table query containing 6 fields..

I have 16 fields in the original table.  The first is a primary key, the next
fifteen and actually three groups of five (a,b,c,d,e,a1,b1,c1,d1,e1,a2,b2,c2,
d2,e2) the need to be output as a,a1,a2 into a field called last name (not
concatenated), b,b1,b2 into a field called first name...etc...

Does anyone have any ideas

0
gz3d2h
6/6/2007 4:07:31 PM
access.queries 6343 articles. 1 followers. Follow

1 Replies
1314 Views

Similar Articles

[PageSpeed] 38

On Wed, 06 Jun 2007 16:07:31 GMT, "gz3d2h" <u34843@uwe> wrote:

>I need to create a make-table query containing 6 fields..
>
>I have 16 fields in the original table.  The first is a primary key, the next
>fifteen and actually three groups of five (a,b,c,d,e,a1,b1,c1,d1,e1,a2,b2,c2,
>d2,e2) the need to be output as a,a1,a2 into a field called last name (not
>concatenated), b,b1,b2 into a field called first name...etc...
>
>Does anyone have any ideas

Sounds like you have a pretty serious normalization problem! A "Normalizing
Union Query" may be the solution, but your rather abstract description has me
wondering what field goes where. You say you want a, a1, a2 into a field
called last name - in three records? one record? 

What's the actual data in a record, and what do you want in the output? Do you
want three records with five fields? Where should the primary key from this
table appear, if anywhere?


             John W. Vinson [MVP]
0
John
6/8/2007 4:24:38 AM
Reply:

Similar Artilces:

How do I sum dollars in one column based on dates in another?
How can I sum the total numbers contained in one column based on dates in another column? Example: I need the total of 5 units sold int the month of January 2006. I have the sale ammounts in one column and the closing dates in another. What forumla would I use for this? Thanks in advance for any help. =SUMPRODUCT(--(MONTH(B2:B50)=1),--(YEAR(B2:B50)=2006),A2:A50) for January change 1 in the month part to a different month -- Regards, Peo Sjoblom Northwest Excel Solutions www.nwexcelsolutions.com (remove ^^ from email address) Portland, Oregon "Alan" <Alan@discus...

Consolidate or Combine Columns
Sir/Madam: Let's say you have two data sets, each with two columns. The first column in each data set is an ID#. In the first data set the second column is "height" and in the second data set the second column is "weight" DATA Set 1 Data Set 2 ID# Height ID# Weight 1 57 1 167 2 60 2 190 3 50 4 122 5 160 I want to combine (merge, consolidate) these into one data set: ID# Height Weight 1 57 167 2 60 190 3 50 4 122 5 ...

inserting a column
I wonder if you can help? I have created a spreadsheet on which I have been able to insert columns until now. Now when I go to insert a column I get the message - "To prevent possible loss of data Excel cannot shif nonblank cells off the work sheet. Try to delete or clear the the cells to the right and below your data. Then select cell A1, and save your workbook to reset the last cell used. Or, you can move the data to a new location and try again." However, I have done both of these and I still get the same error message all cells at the end of the sheet are clear so th...

How did I make my column headings become numeric (1 2 3)
You asked in the subject line instead of the message body and you did tools>options>settings>r1c1 -- Don Guillett SalesAid Software dguillett1@austin.rr.com "averyemma" <averyemma@discussions.microsoft.com> wrote in message news:D5B23A7C-E916-4A27-BE79-3BE9F3825762@microsoft.com... > ...

Multipule outlook profiles
I have 2 mail services profiles (on Outlook 2000) each on its own MS Exchange server (Exchange 5.5), is it posible to log onto just one profile and have permissions for both? Sure... Delegate control in one of your profiles to the other and that should work just fine :-) Good LucK. Sla >-----Original Message----- >I have 2 mail services profiles (on Outlook 2000) each on >its own MS Exchange server (Exchange 5.5), is it posible >to log onto just one profile and have permissions for both? >. > ...

switching rows with columns..is it possible?
Hallo, I'd like to know if I could swap data from rows and columns: e.g. from: 1 2 3 4 a b c d in: a b c d 1 2 3 4 without doing too much edit or similar... I have a huge raw data file that needs to get sorted. th -- nixis0 ----------------------------------------------------------------------- nixis00's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1475 View this thread: http://www.excelforum.com/showthread.php?threadid=26380 Hi you can copy the range & then paste /special - transpose - i would suggest doing the paste in a new worksheet ...

How do I sort multiple columns of of numerical and alphanumerical.
Example: Column 1 Column 2 Column 3 Column 4 Column 5 1 9 09A1 0023 delete 3 11 11D4 1057 revise I would like to sort first on column 1, then 2, then 3, then 4. When I tried this, it messed up all the numbers in column 4. Help please Select all 5 columns then sort by column 1. Then sort by column 2 as first sort key, column 2 as second, and column 3 as third. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "slowmo" <slowmo@discus...

Addition of two Column with Condition
Hi I need a formula that calculate two or more column with given condition, I used SUMIF and DSUM , but it cant worked. On Feb 21, 4:12=A0am, Tayyab <sheikhtay...@gmail.com> wrote: > I need a formula that calculate two or more column with given > condition, I used SUMIF and DSUM , but it cant worked. Difficult to answer without a specific example. Perhaps one of the following will help: =3DSUMIF(A1:A100,"<100",B1:B100)+SUMIF(A1:A100,"<100",C1:C100) or =3DSUMPRODUCT((A1:A100<100)*(B1:C100)) ...

How to make each column of data a new worksheet?
Hello, I was hoping somebody could help me with an Excel problem - I am not a very advanced excel user but I am hoping someone can give me some simplified advice. Basically I have got an Excel 2007 worksheet with 250 odd columns and I need to split each column of existing data onto a new worksheet. Column A is actually my list of headings, so ideally I would like to extract Columns A and B into one worksheet, then Columns A and C, then Columns A and D etc, all into new worksheets in the same workbook without having to manually copy and paste each of them. I am startin...

Chart column width
How do I make a chart column wider in Excel 2003? Hi, You can increase the column width via the Format Series dialog, Series Options > Gap Width. Reduce gap width value. If you have skinny columns due to dates as category labels then set the Axes to Category rather than Time series or Automatic. Right click chart > Chart Options > Axes. Cheers Andy -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info "IanP" <IanP@discussions.microsoft.com> wrote in message news:7E1C1D7F-3EDE-4DAC-A131-36D19EB9B697@microsoft.com... > How do I make a chart column wid...

Multipule mailboxes
I have SBS 2000 with exchange 2000 I was setting up a user with a exchange account. But when trying to connect it said could not find mailbox. I looked at the exchange mail boxes and could not see one for the user so I tried adding a mail box again, finally it worked and outlook connected to the account but when trying to send and receive mail, it errored on receiving. I looked again in the exchange mailboxes folder and I saw 2 accounts for the same user. I don't know how this happed, both have some mail in them, I don't want to lose, how do I fix this problem? Decide which ...

Column headings question
I have Excel configured with Column Headings in "numbers" rather than in "letters". How do I change the Column Headings back to letters? The way the worksheet looks now is: 1 2 3 4 5 ... 1 2 3 Thanks for your help, Mark Flynn Tools/Options/General - deselect R1C1 reference style Bob Umlas Excel MVP "Mark Flynn" <mark.flynn@uci.net> wrote in message news:%23lI7RIgfHHA.444@TK2MSFTNGP04.phx.gbl... > I have Excel configured with Column Headings in "numbers" rather than in > "letters". > How do I change the Colu...

Transpose Large Column to Row
Hi - I have a column with about 25,000+ rows in Excel 2007, and need to somehow get this transposed into a row (so with 25,000+ columns). I have tried to do this with the TRANSPOSE function using Ctrl-C and using the Paste drop-down menu and selecting the Transpose option. But I get an error message that the copy area and paste area are not the same size. I presume this is because I cannot paste across 25,000+ columns? Basically, in the end, what I want to have is a list of each cell with a semicolon after each but am just not sure how to do this. Any suggestions would ...

Column
I have difficulty with Word... I have a letter with column. This is my document type. But I would like to cut and paste this one page in other new document. So when I paste my page (with column in top) my document became in 2 pages. I can't put my document in just one page...help me please ! ...

Filling sliding accross and along columns
Hi all, Can anybody tell me how can I slide a formula contsinaing a constant across lines and along columns. Here is an example a11 a12 a13 a21 a22 a23 a31 a32 a33 What I would like to have is a11 a12 a13 a21/a11 a22/a11 a23/a11 : For this I use =CELL/$CELL$XX and slide across a31/a11 a32/a11 a33/a11 : Seems like sliding the previous formula along columns is not working. Cheers kaci, just a suggestion for you to play around with and figure the solutio our for yourself. for the divisor, try $A$1, $A1, A$1 in order to anchor the cell, th column, the row respectively. mac -...

Sum a column of variable length?
I have to build a workbook where columns of data will be cut and pasted into Tab 1, then the column totals need to appear automatically on Tab 2. The problem is that the number of rows of data in Tab 1 will be variable and unpredictable, although the columns will always start in a known row. I might be able to do it by something like =SUM('Tab 1'!I7:I65535) but is there a more precise way? Here is one way =SUM(OFFSET('Tab 1'!I7,,,'Tab 1'!COUNTA(I:I)-'Tab 1'!COUNTA(I1:I6))) -- HTH RP (remove nothere from the email address if mailing direct) "Br...

How do widen columns in a column chart?
I tried the other suggestions posted, but it still doesn't work. I have 5 columns that are pretty narrow and I would like to have them 3x wider with a very small gap in between the columns. I put the x-axsis to category, but I still can't widen the columns. I am not sure if the chart being copied from PowerPoint would make a difference. Thanks "ITMasterA" wrote: > I tried the other suggestions posted, but it still doesn't work. I have 5 > columns that are pretty narrow and I would like to have them 3x wider with a > very small gap in between the colum...

Need to sum up numerous columns in different worksheet into 1
Hi, I generate 100-200s .csv files end of each month for my company. Each of these .csv files have numerous data that I need to sum up for a report. (Example: 1.csv have 3 columns with things like, item A, item C, item D etc and 2.csv have columns with item B, item C and item E... and so on and so on) I am required to sum up all these 100-200s .csv files and have a total sum of how many of each items appeared and used by the users. (Example: item A - total count 100, item B - total count 20.. .etc etc) Is there a way I could do it without going through each of the .csv files and counti...

How do I rename my columns? (i.e.
Just trying to get specific info at the top of my spreadsheet where (A1, B1, C1....etc) are. Not sure quite how to do it. Any assistance would be appreciated. Thanks in advance. Hi, You cannot rename the columns, you can use the first row to enter your names instead "Novice" wrote: > Just trying to get specific info at the top of my spreadsheet where (A1, B1, > C1....etc) are. Not sure quite how to do it. Any assistance would be > appreciated. Thanks in advance. Use row 1. If the A1, B1, bother you, Tools - Options - View, hide column Headers. -- ...

Delete Columns
In Excel, I need a macro that will delete a column based on a cell that will contain a name based on an input box. Thanks, Bernie See you OTHER post. -- Don Guillett SalesAid Software dguillett1@austin.rr.com "Bernie" <bml66@aol.com> wrote in message news:1172699736.687460.300590@h3g2000cwc.googlegroups.com... > In Excel, I need a macro that will delete a column based on a cell > that will contain a name based on an input box. > > Thanks, > Bernie > ...

Moving multipule sheets into their own workbooks
I have a workbook with many spreadsheets in it for customer pricing, each customer has a sheet of their own. I need to email each particular sheet as an attachment to each customer, is there a quick easy way to have each sheet copied and pasted into its own workbook in one or few easy steps instead of doing each one separate? I know I've done this before and can't remember what I did, LOL. Any help would be appreciated. Thanks! Kathy in WI Hi Kathy Try something like this http://www.rondebruin.nl/mail/folder1/mail5.htm Or http://www.rondebruin.nl/mail/folder2/mail5.htm...

how to access column 8 in my Range
Hi, I want to place "1" in column 8 in every row in my range named "LegendList". How do I do this? I tried: LegendList(, 8) = "1" and I tried With LegendList .Cells(, 8) = "1" end with Neither works... Help...please Thanks, Paul try this to put the number 1 in each cell Sub oneinrange() Range("legendlist") = 1 End Sub or 8 columns over Sub oneinrange() Range("legendlist").Offset(, 8) = 1 End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com "rpsmith" <rpsmith@bpa.gov> wrot...

Column Size
Hello I've been playing around with placing objects in specified cells. However, I've noticed that the standard column width is shown as 8.43 (64 pixels) - can you tell me what this measurement is? I think the standard row height is in points - why the difference? Thanks Peter Width is dependant on the font. 8.43 denotes that many characters can be displayed in the default Arial font. Try this: With the display set at 100%, note how many columns are visible on your screen. Now, <Tools> <Options> <General> tab, And change the default font to Algerian (right ab...

Change measurements for columns etc
Hi Can I change the measurements to cm or inch when I format columns. Thanks, Mon Excel 2002 "MonB" wrote: > Hi Can I change the measurements to cm or inch when I format columns. Thanks, > Mon No, you cannot not best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "MonB" <MonB@discussions.microsoft.com> wrote in message news:500EDD94-AFA5-4B39-90DE-75BD6935D947@microsoft.com... > Hi Can I change the measurements to cm or inch when I format columns. > Thanks, > Mon Columns("A").Columnwidth = 13 will...

Lookup two columns
I want to compare the contents of two (adjacent) cells in one sheet with two adjacent cells in another sheet (within one workspace) and if the *pair* of cells are the same, deliver the value in the cell a few columns along (if you know what I mean - like lookup but comparing two cells). The cells are not sorted. Any ideas? Cheers. Bobby If you are comparing A1-B1 sheet 1 to A1-B1 sheet 2, then =IF(AND(Sheet1!A1=Sheet2!A1,Sheet1!B1=Sheet2!B1),"They match","no match") If you have to "lookup" A1-B1 against the whole columns of A and B on sheet2, then kyou co...