Merge a spreadsheet into an already created table

[bitsbb01]

Hi all,
Ok this is going to probably be an easy one, however as i've not done this at all yet.. i've been having problems merging a spreadsheet with data into an already created table..
the main things in comman is the "MC", "First Name", "Last Name".
I'm trying to update dates etc that are on the spreadsheet exported from another database, and update my datebase with those new dates..

however the import features dont work for this.. and i've already tried following various tuts on Google but to no avail..

Any help would be really greatfull..

 

[Michael3Smith]

Don't import the data directly into the table you want to update. Import the data into another table (let's call that table "ImportUpdate"), then update the data via an update query (or through code), then delete the data from "ImportUpdate".

The tools to import data into Access just do that, and nothing else. In other words, they just import.

 

[bitsbb01]

The problem i'm having with it doing an update query is it doesnt update it...

 

[JHB]

Is the some error message, then show it (both number and description).
Show the SQL-string for the update query,
Check also that the field type is the correct type for what you want to put into the field.

 

[bitsbb01]

ok tried a few different ways.. 1
that shows over 295488 records, of which theres 30+ records per person, that's incorrect there should only be 1 record per person

UPDATE DataExport73201, CopyOfCases SET CopyOfCases.MC = DataExport73201.MC, CopyOfCases.[Assigned To] = [DataExport73201].[Assigned To], CopyOfCases.[First Name] = [DataExport73201].[First Name], CopyOfCases.[Last Name] = [DataExport73201].[Last Name], CopyOfCases.[SP Begin Date] = [DataExport73201].[SP Begin Date], CopyOfCases.[SP End Date] = [DataExport73201].[SP End Date];

and 2nd tried with a Union Query, that shows them both merged however shows the duplicates.. for example

select [Assigned To], [First Name], [Last Name], [MC], [Effective Date], [Status], [SP End Date], [SP Begin Date], [DOB Of youngest], [DOB], [SSField2], [E-mail Address]
from DataExport73201
UNION ALL 
select [Assigned To], [First Name], [Last Name], [MC], [Effective Date], [Status], [SP End Date], [SP Begin Date], [DOB Of youngest], [DOB], [SSField2], [E-mail Address]
from CopyOfCases;

all i'm trying to do is pull whats changed from DataExport73201 table to CopyOfCases, mostly the [SP End Date] changes, so need the new date added to the CopyOfCases current clients..

 

[JHB]

ok tried a few different ways.. 1
that shows over 295488 records, of which theres 30+ records per person, that's incorrect there should only be 1 record per person

UPDATE DataExport73201, CopyOfCases SET CopyOfCases.MC = DataExport73201.MC, CopyOfCases.[Assigned To] = [DataExport73201].[Assigned To], CopyOfCases.[First Name] = [DataExport73201].[First Name], CopyOfCases.[Last Name] = [DataExport73201].[Last Name], CopyOfCases.[SP Begin Date] = [DataExport73201].[SP Begin Date], CopyOfCases.[SP End Date] = [DataExport73201].[SP End Date];

You need a WHERE clause. It could be the first and last name, if you don't have an unique ID.

 

[bitsbb01]

they both have MC which is unique to both, as thats the identifier for the client.

 

[bitsbb01]

ok, so if i do it like this..

UPDATE DataExport73201, CopyOfCases SET CopyOfCases.[Assigned To] = [DataExport73201].[Assigned To], CopyOfCases.[First Name] = [DataExport73201].[First Name], CopyOfCases.[Last Name] = [DataExport73201].[Last Name], CopyOfCases.[SP Begin Date] = [DataExport73201].[SP Begin Date], CopyOfCases.[SP End Date] = [DataExport73201].[SP End Date]
WHERE CopyOfCases.MC=[DataExport73201].[MC];

then i get type mismatch in expression

 

[JHB]

Then the values isn't the same type - ex. one number the other text.
Open both table - look at the field type for MC.

 

[Mita]

Hi,

I am creating a table where i just import the analysis from a excel sheet. After importing I found out that the date format is wrong...e.g 4/13/2011 where it should be 13/4/2011. I am trying to change it but it cant....


Can anione help me with this...!!!!

Thanks
Mita.

 

[bitsbb01]

Ah ok, well fixed that, and now it shows 1050 records however in copyofcases theres 2031 records and in dataexport73201 theres 1158 records, so its only doing an update to the one's in the current database, but how can i get it in the same query to also pull all the other records, so the 1 datasheet has all of the records including the new one's and updated..

 

[bitsbb01]

ok i'm confused now..

i've got all my data that i want updated into the db in a table called NETANFImports, and the main DB Table called CopyOfCases

however when i run the SQL Query it doesnt seem to update the Copy of Cases instead it replaces the NETANFImports with data from CopyOfCases

UPDATE NETANFImports, CopyOfCases SET 
[NETANFImports].[Status] = [CopyOfCases].[Status], 
[NETANFImports].[Tracking Comments] = [CopyOfCases].[Comments], 
[NETANFImports].[Email Address] = [CopyOfCases].[E-mail Address], 
[NETANFImports].[Phone Number] = [CopyOfCases].[Home Phone],
[NETANFImports].[Address] = [CopyOfCases].[Address], 
[NETANFImports].[City] = [CopyOfCases].[City], 
[NETANFImports].[State] = [CopyOfCases].[State/Province], 
[NETANFImports].[Zip Code] = [CopyOfCases].[ZIP/Postal Code], 
[NETANFImports].[Master Case Number] = [CopyOfCases].[MC], 
[NETANFImports].[Last Name] = [CopyOfCases].[Last Name], 
[NETANFImports].[First Name] = [CopyOfCases].[First Name],
[NETANFImports].[Assigned Worker] = [CopyOfCases].[Assigned To],
[NETANFImports].[Date Of Birth] = [CopyOfCases].[DOB],
[NETANFImports].[csworker] = [CopyOfCases].[csworker],
[NETANFImports].[SSField] = [CopyOfCases].[SSField],
[NETANFImports].[Required Hours] = [CopyOfCases].[Hours],
[NETANFImports].[Service Plan End] = [CopyOfCases].[SP End Date],
[NETANFImports].[Imported By] = [CopyOfCases].[Imported By],
[NETANFImports].[Last Contact] = [CopyOfCases].[Last Contact],
[NETANFImports].[Service Plan Begin] = [CopyOfCases].[SP Begin Date],
[NETANFImports].[EF Contract Signature Date] = [CopyOfCases].[EF Contract Signed],
[NETANFImports].[Effective Date] = [CopyOfCases].[Effective Date],
[NETANFImports].[Assessment] = [CopyOfCases].[Assessment Date],
[NETANFImports].[Interpreter Needed] = [CopyOfCases].[Interpreter],
[NETANFImports].[Birth Date of Youngest Child] = [CopyOfCases].[DOB Of youngest],
[NETANFImports].[SSN] = [CopyOfCases].[SSField2],
[NETANFImports].[Household Type] = [CopyOfCases].[Hoursehold Type],
[NETANFImports].[Month] = [CopyOfCases].[Month of import to db]
WHERE (((CopyOfCases.MC)=[NETANFImports].[Master Case Number]) AND ((CopyOfCases.[Last Name])=[NETANFImports].[Last Name]) AND ((CopyOfCases.[First Name])=[NETANFImports].[First Name]));

Any advice would be really greatfull as pulling my head out and have tried switching them all around etc.. and still no luck...

 

[JHB]

It is down to the base level, data flows from right to left.
This means variables, controls or otherwise, on the left side of the equal sign is assigned the value of variables, controls or otherwise from the right side.
YourVariabel = 3; means YourVariabel is assigned the value 3
YourVariabelOnTheLeftSide = YourVariabelOnTheRigthSide; YourVariabelOnTheLeftSide is assigned the value of YourVariabelOnTheRigthSide.
When we look at your code, we have [NETANFImports].[Status] on the left side and [CopyOfCases].[Status] on the right side, so [NETANFImports].[Status] is assigned the value from [CopyOfCases].[Status].
Is it clear enough what happens?

From a former post:

... but how can i get it in the same query to also pull all the other records, so the 1 datasheet has all of the records including the new one's and updated..

You can't do it in the same query, you need to build and run an unmatched query after you have updated [CopyOfCases]. Use the query wizard to build that query.

 

[bitsbb01]

yep, i relized what was meant to happen about 10 mins after i left work yesterday..

However its still not doing what its meant to do..
I understand its meant to update the CopyOfCases with the data from NETANFImports, however when i run it, it comes up with the Value box for every cell, however in the table for each cell it does have contents.. and ideas..

This is what the Query now looks like & when i run it the 1st box comes up for entering text & says NETANFImports.Status

UPDATE NETANFImports, CopyOfCases SET 
[CopyOfCases].[Status] = [NETANFImports].[Status], 
[CopyOfCases].[Comments] = [NETANFImports].[Tracking Comments], 
[CopyOfCases].[E-mail Address] = [NETANFImports].[Email Address], 
[CopyOfCases].[Home Phone] = [NETANFImports].[Phone Number],
[CopyOfCases].[Address] = [NETANFImports].[Address], 
[CopyOfCases].[City] = [NETANFImports].[City], 
[CopyOfCases].[State/Province] = [NETANFImports].[State], 
[CopyOfCases].[ZIP/Postal Code] = [NETANFImports].[Zip Code],
[CopyOfCases].[MC] = [NETANFImports].[Master Case Number],
[CopyOfCases].[Last Name] = [NETANFImports].[Last Name],
[CopyOfCases].[First Name] = [NETANFImports].[First Name],
[CopyOfCases].[Assigned To] = [NETANFImports].[Assigned Worker],
[CopyOfCases].[DOB] = [NETANFImports].[Date Of Birth],
[CopyOfCases].[csworker] = [NETANFImports].[csworker],
[CopyOfCases].[SSField] = [NETANFImports].[SSField],
[CopyOfCases].[Hours] = [NETANFImports].[Required Hours],
[CopyOfCases].[SP End Date] = [NETANFImports].[Service Plan End],
[CopyOfCases].[Last Contact] = [NETANFImports].[Last Contact],
[CopyOfCases].[SP Begin Date] = [NETANFImports].[Service Plan Begin],
[CopyOfCases].[EF Contract Signed] = [NETANFImports].[EF Contract Signature Date],
[CopyOfCases].[Effective Date] = [NETANFImports].[Effective Date],
[CopyOfCases].[Assessment Date] = [NETANFImports].[Assessment],
[CopyOfCases].[Interpreter] = [NETANFImports].[Interpreter Needed],
[CopyOfCases].[DOB Of youngest] = [NETANFImports].[Birth Date of Youngest Child],
[CopyOfCases].[SSField2] = [NETANFImports].[SSN],
[CopyOfCases].[Hoursehold Type] = [NETANFImports].[Household Type],
[CopyOfCases].[Imported By] = [NETANFImports].[Imported By],
[CopyOfCases].[Month of import to db] = [NETANFImports].[Month]
WHERE (((CopyOfCases.MC)=[NETANFImports].[Master Case Number]) AND ((CopyOfCases.[Last Name])=[NETANFImports].[Last Name]) AND ((CopyOfCases.[First Name])=[NETANFImports].[First Name]));

I added a zip with excel copys of CopyOfCases & NETANFIMPORTS & This Query, as it just does not want to run this query..


Oh and MITA, right click the Table, goto design table, then goto the data type for your dates, click it, then goto General down the bottom, and in format put d/m/yyyy
that'll do it for you

 

[JHB]

By me the query runs ok, I've attached the sample database.
Maybe your table structure isn't the same, field-type etc.

 

[bitsbb01]

yep using your table it works fine, my table does have validation, could that be whats stoping it from running correctly

 

[JHB]

Yes it could be that, what type of validation and where?
Show you table structure - print screen, (zip it if you haven't post 10 post), or post you database with some sample data, (zip it).

 

[bitsbb01]

Ok, tried again, making sure all fields were same types etc and still it does it..
So heres a copy, with the cases, import and query..

 

[JHB]

There is some problem with the field "Comments" in the table "Cases", maybe corrupt in a way. (If you add another Memo field in and choose to update this instead of "Comments", the update query run, if you also change the below.)

Ok, tried again, making sure all fields were same types etc and still it does it..

Not quiet correct, then you have different field type for the field "Interpreter" and "Interpreter Needed".
If you excluded the two fields from the update query, it will run.

Change "Interpreter" or "Interpreter Needed" to same field type.

 

[bitsbb01]

Didn't think it'd be the comment section.. thought I'd turned off the interpreter party