Combining records in access 2007

I have imported a text file into access 2007.  However I guess when
the person that exported the text files(i think they are from a sql
server db), what was one record in the sql server db, is now broken
down into two records in the text file.

I am needing to try to take the text files once imported into a table
in access to combine two records into one.  I just have a autonumber
field that is the primary key for each field.

So my table looks like this(original_table):

Column 1     Column 2     Column 3
   1                  ABC           DEF
   2                  GHI            JKL
   3                  MNO          PQR
   4                  STU           VWX

Result would look like(result table)

Column 1   Column 2      Colummn 3    Column 4    Column 5
   1               ABC              DEF            GHI
JKL
   2              MNO              PQR           STU               VWX

 The problem is that there is about 20,000 records in the database,
and I have 55 tables.

I am all new to this,  I would greatly appreciate some help.  If doing
it before going into access that would work to, I just need the end
result to look like the result table.

Thank you.
0
barmatt80
12/3/2007 5:36:53 AM
access 16762 articles. 3 followers. Follow

4 Replies
610 Views

Similar Articles

[PageSpeed] 6

Hi,
Can you reply with your 'Text to be imported in access' , It will help me to 
help you.

JB

"barmatt80@gmail.com" wrote:

> I have imported a text file into access 2007.  However I guess when
> the person that exported the text files(i think they are from a sql
> server db), what was one record in the sql server db, is now broken
> down into two records in the text file.
> 
> I am needing to try to take the text files once imported into a table
> in access to combine two records into one.  I just have a autonumber
> field that is the primary key for each field.
> 
> So my table looks like this(original_table):
> 
> Column 1     Column 2     Column 3
>    1                  ABC           DEF
>    2                  GHI            JKL
>    3                  MNO          PQR
>    4                  STU           VWX
> 
> Result would look like(result table)
> 
> Column 1   Column 2      Colummn 3    Column 4    Column 5
>    1               ABC              DEF            GHI
> JKL
>    2              MNO              PQR           STU               VWX
> 
>  The problem is that there is about 20,000 records in the database,
> and I have 55 tables.
> 
> I am all new to this,  I would greatly appreciate some help.  If doing
> it before going into access that would work to, I just need the end
> result to look like the result table.
> 
> Thank you.
> 
0
Utf
12/3/2007 1:33:01 PM
On Dec 3, 12:36 am, barmat...@gmail.com wrote:
> I have imported a text file into access 2007.  However I guess when
> the person that exported the text files(i think they are from a sql
> server db), what was one record in the sql server db, is now broken
> down into two records in the text file.
>
> I am needing to try to take the text files once imported into a table
> in access to combine two records into one.  I just have a autonumber
> field that is the primary key for each field.
>
> So my table looks like this(original_table):
>
> Column 1     Column 2     Column 3
>    1                  ABC           DEF
>    2                  GHI            JKL
>    3                  MNO          PQR
>    4                  STU           VWX
>
> Result would look like(result table)
>
> Column 1   Column 2      Colummn 3    Column 4    Column 5
>    1               ABC              DEF            GHI
> JKL
>    2              MNO              PQR           STU               VWX
>
>  The problem is that there is about 20,000 records in the database,
> and I have 55 tables.
>
> I am all new to this,  I would greatly appreciate some help.  If doing
> it before going into access that would work to, I just need the end
> result to look like the result table.
>
> Thank you.

How about creating a query called Odd with SQL:
SELECT Table1.[Column 1], Table1.[Column 2], Table1.[Column 3]
FROM Table1
WHERE (((([Column 1]+1)/2=Int(([Column 1]+1)/2))=True));

another query called Even with SQL:
SELECT [Column 1]-1 AS MatchColumn1, Table1.[Column 2], Table1.[Column
3]
FROM Table1
WHERE ((([Column 1]/2=Int([Column 1]/2))=True));

and a third query with SQL:
SELECT Odd.*, Even.[Column 2] AS [Column 4], Even.[Column 3] AS
[Column 5]
FROM Odd INNER JOIN Even ON Odd.[Column 1] = Even.MatchColumn1;
0
Kerry
12/3/2007 8:44:50 PM
On Dec 3, 3:44 pm, Kerry <kerry...@yahoo.com> wrote:
> On Dec 3, 12:36 am, barmat...@gmail.com wrote:
>
>
>
>
>
> > I have imported a text file into access 2007.  However I guess when
> > the person that exported the text files(i think they are from a sql
> > server db), what was one record in the sql server db, is now broken
> > down into two records in the text file.
>
> > I am needing to try to take the text files once imported into a table
> > in access to combine two records into one.  I just have a autonumber
> > field that is the primary key for each field.
>
> > So my table looks like this(original_table):
>
> > Column 1     Column 2     Column 3
> >    1                  ABC           DEF
> >    2                  GHI            JKL
> >    3                  MNO          PQR
> >    4                  STU           VWX
>
> > Result would look like(result table)
>
> > Column 1   Column 2      Colummn 3    Column 4    Column 5
> >    1               ABC              DEF            GHI
> > JKL
> >    2              MNO              PQR           STU               VWX
>
> >  The problem is that there is about 20,000 records in the database,
> > and I have 55 tables.
>
> > I am all new to this,  I would greatly appreciate some help.  If doing
> > it before going into access that would work to, I just need the end
> > result to look like the result table.
>
> > Thank you.
>
> How about creating a query called Odd with SQL:
> SELECT Table1.[Column 1], Table1.[Column 2], Table1.[Column 3]
> FROM Table1
> WHERE (((([Column 1]+1)/2=Int(([Column 1]+1)/2))=True));
>
> another query called Even with SQL:
> SELECT [Column 1]-1 AS MatchColumn1, Table1.[Column 2], Table1.[Column
> 3]
> FROM Table1
> WHERE ((([Column 1]/2=Int([Column 1]/2))=True));
>
> and a third query with SQL:
> SELECT Odd.*, Even.[Column 2] AS [Column 4], Even.[Column 3] AS
> [Column 5]
> FROM Odd INNER JOIN Even ON Odd.[Column 1] = Even.MatchColumn1;- Hide quoted text -
>
> - Show quoted text -

Sorry guys, I have been away on familly business.  I will try what
kerry said and see if i can get it to work.  Thanks.
0
barmatt80
12/20/2007 2:03:07 AM
On Dec 3, 3:44 pm, Kerry <kerry...@yahoo.com> wrote:
> On Dec 3, 12:36 am, barmat...@gmail.com wrote:
>
>
>
>
>
> > I have imported a text file into access 2007.  However I guess when
> > the person that exported the text files(i think they are from a sql
> > server db), what was one record in the sql server db, is now broken
> > down into two records in the text file.
>
> > I am needing to try to take the text files once imported into a table
> > in access to combine two records into one.  I just have a autonumber
> > field that is the primary key for each field.
>
> > So my table looks like this(original_table):
>
> > Column 1     Column 2     Column 3
> >    1                  ABC           DEF
> >    2                  GHI            JKL
> >    3                  MNO          PQR
> >    4                  STU           VWX
>
> > Result would look like(result table)
>
> > Column 1   Column 2      Colummn 3    Column 4    Column 5
> >    1               ABC              DEF            GHI
> > JKL
> >    2              MNO              PQR           STU               VWX
>
> >  The problem is that there is about 20,000 records in the database,
> > and I have 55 tables.
>
> > I am all new to this,  I would greatly appreciate some help.  If doing
> > it before going into access that would work to, I just need the end
> > result to look like the result table.
>
> > Thank you.
>
> How about creating a query called Odd with SQL:
> SELECT Table1.[Column 1], Table1.[Column 2], Table1.[Column 3]
> FROM Table1
> WHERE (((([Column 1]+1)/2=Int(([Column 1]+1)/2))=True));
>
> another query called Even with SQL:
> SELECT [Column 1]-1 AS MatchColumn1, Table1.[Column 2], Table1.[Column
> 3]
> FROM Table1
> WHERE ((([Column 1]/2=Int([Column 1]/2))=True));
>
> and a third query with SQL:
> SELECT Odd.*, Even.[Column 2] AS [Column 4], Even.[Column 3] AS
> [Column 5]
> FROM Odd INNER JOIN Even ON Odd.[Column 1] = Even.MatchColumn1;- Hide quoted text -
>
> - Show quoted text -

Actually I just noticed that I was wrong in my original post:  My
columns within access are:

Col1     Col2     Col3     Col4     Col5
  1        abc      def       ghi        jkl
  1        mno     pqr      stu        vwx
  1        yz        aaa     bbb       ccc
  2        ddd      ee        fff         g
  2        h          iiiii      jjj          kk

I am needing to combine the records where col1 values are equal.

Sorry for the mix up.
0
barmatt80
12/20/2007 2:09:49 AM
Reply:

Similar Artilces:

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...

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...

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...

Macro for Sharepoint List and another Table?? Same # records?
I am creating a database which we have a form made up of prepopulated data (from a sharepoint list) and also data input needed by a user (for example, comments, etc.). I have two tables: one sharepoint list that is linked to sharepoint website and another table that houses the additional user inputs [table 2]. I have linked the files based on the primary key. So, every record in my table 2 has a plus sign and it will expand and you will see the linked data from the sharepoint list. Is there a way that I can automatically create a record for every corresponding record ...

Outlook 2007 Search always returns no matches
Any search I do in Outlook 2007's new search box returns no matches. I watched the Indexing Status as it progressed, and now it says "Outlook has finished indexing all of your items" But, searching on anything, even the word "The" or "a" returns no results. I'm searching all Outlook items. I'm on an Exchange Server via VPN. Anyone have any suggestions? I uninstalled Office and re-installed and the problem went away... "Tom" <none@none.com> wrote in message news:%23yGP6NPOHHA.2140@TK2MSFTNGP03.phx.gbl... > Any search I do i...

Publisher 2007 frequently crashes
Hello all, I'm having some difficulty with Publisher 2007. Whenever I try to open a particular file, the program crashes. I get no error message, except that Publisher needs to close. I've run Office Diagnostics (twice), with no results. I can open other Publisher files. The file that I am trying to open is an internal website for the company I work for. This file began acting strangely a couple of weeks ago - it wouldn't insert a duplicate page for a while, but I found a workaround, so I didn't worry about it. However, now I think that it may be a part of a larger probl...

Add PowerPoint 2007 to Office Basic
I have Office Basic installed on my laptop and need to add PowerPoint. What is the most recommended, easiest and least expensive way to do this? ...

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...

Outlook 2007 Calendar -> Outlook 2003
In the past I could forward appointments and birthdays from one PC to another, if both PC's had Office 2003. I just tried forwarding a birthday from Outlook 2007 to Outlook 2003 and it doesn't work. I get the email in 2003. Open it, open the calendar item, then click on Save & Close. No error message. But it's not there. Neither in the current month or for next year. I forwarded it as a reoccurring item, that's the only way it will let you do that. Has anyone else been able to do this? Or, is this a backward compatibility issue? It's odd.... this works fine for...

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....

Creating Text Box in Publisher 2007 Crashes the Application
Hello, we have a clean install of Publisher 2007 under Windows XP SP2, and when we try to create a text box in a document, (both an existing document and a blank document), publisher crashes. Office is fully patched. I ran Office Diagnostics from the help menu and no problems were found and the issue persists. Any thoughts on how to resolve the issue? Thanks, Syd See if selecting a different printer as default helps. How to view error signatures if an Office program experiences a serious error and quits http://support.microsoft.com/kb/289508/en-us -- Mary Sauer MSFT MVP http://of...

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,...

me too, problem with import... only 15 records
me too, problem with import... only 15 records.... also in one of my customers crm i need a fast answer.... I dont see a question to answer...? JUAN MARKETING wrote: > me too, problem with import... only 15 records.... also in one of my > customers crm > > i need a fast answer.... Hehe yeah, what is the question!? what are you trying to import? Sorry, I don't know the reason, bu each time i try to import x number of accounts or contacts, MSCRM only import 15 records... Promise, everything is ok... columns, format csv from excel, size, records.... I say that, becau...

MS Project 2007 question
Hello, I am developing a simple schedule with no resources but 2 tasks out of 100 are hazardous and I would like to make the schedule so that when these tasks occur, no other tasks can occur. In other words, even though all tasks are moving around as the schedule is being developed, I would like to make sure no other tasks can be scheduled wherever these 2 hazardous tasks fall. These 2 tasks are serial to every other task in the schedule. Is there a way to do this? Thanks for your help. Mike Create two milestones. Use milestone 1 as the successor task to all tasks th...

Office 2007 Templates & Themes
Is there anyway the content for Office 2007 at the MS Office web site could be made available to Office:Mac users. Currently need to have OGA validation via ActiveX control or from Office for Windows programs. Surely Mac users could be given access! Dave We have asked that question. We don't have an answer yet (at least, it wasn't "No", that would have been quick!). They are trying to work out how to let us in without letting in all the people using the competitor products. I think they will set up a mirror on the MacTopia site that will allow us to reach the ones tha...

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...

Subquery
Hi, I'm trying to code a subquery on a table but to no avail...it's a very simple table that has Date (short date, just recording the day) and NAV which is a number (double). All I'm wanting to do is have a query that displays the Date, the NAV and the NAV from the day before - only recording business dates. In other words, the subquery number I want is the NAV from the record preceeding the current one. I've got as far as the below, but that just gets the same NAV number as today's: SELECT tblNAV.Date, tblNAV.NAV, (SELECT LAST(x.NAV) FROM tblNAV as X LEFT JOIN tblNAV on...

How to Identify Records with Overlapping Dates
I need to be able to systematically identify any instance where there are overlapping date ranges in a data set. I need to pull records like those listed below out of a larger data set. I previously posted a question similar to this and was advised to pull the same table in a query, match on Member Number, and qualify that the product code from TableA did not match product code in TableA_1 where the Begin Date TableA was < Term date TableA_1 and TableA_1 Begin Date < TableA End Date. Which worked great for me finding overlapping records where the product was different, bu...

Outlook 2007 PST files and temp files
When I have PST files on the server, I notice that there is a temp file accompanying it when Outlook is loaded. Would loading the temp file on the local PC make Outlook quicker, if so, how can one do this...? Thanks, Tom... "TK" <tkarpowski@bennettcompany.com> wrote in message news:Ovq2RUgvJHA.1088@TK2MSFTNGP04.phx.gbl... > When I have PST files on the server, I notice that there is a temp file > accompanying it when Outlook is loaded. > > Would loading the temp file on the local PC make Outlook quicker, if so, how > can one do this...? You shouldn...

Setting in Outlook 2007
Hi I have Outlook 2007 with all updates, how do I change the settings so attachments, web addresses or inserted pictures are not removed or disallowed. Thanks for any help regards Daniel Attacments removed? - Uninstall your anti virus application Web addresses - In internet options ensure outlook is set as default mail app "Daniel" <noone@nowhere.com> wrote in message news:OcOS1y97JHA.5356@TK2MSFTNGP05.phx.gbl... > Hi > > I have Outlook 2007 with all updates, how do I change the settings so > attachments, web addresses or inserted pictures are not removed...

Exchange 2007 CCR after reseeding storage group copies
We have an Exchange 2007 CCR cluster that sits on two Windows Server 2008 Enterprise nodes. Due to problems with one node, I evicted it from the cluster and rebuilt it, reinstalling Exchange using the custom / install passive node setup. I used 'Update Storage Group Copy' (with the option selected to 'Delete any existing log files in the target path') to reseed the active databses. It seemed to go OK, and the storage groups status in the Database Management window became 'healthy'. However, when I tried to move the clustered mailbox server on to the new node f...

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...