sql - MS Access 2007 update/insert or merge or union -

-

i have been searching quite while how should pretty easy in ms access 2007.

i have main table, items_moved, tracks number of files received, type, day. basic structure is:

id (autonumber), drive (text), type (text), date (datetime), file_count (number), file_size (number)

typical data looks like:

1777, f:\snaps, pics, 6/09/2010, 66, 151616131 1778, f:\snaps, pics, 6/10/2010, 5, 464864 1779, g:\pics, pics, 6/09/2010, 58, 45645646 1780, g:\pics, pics, 6/10/2010, 70, 123456667

i have temp table exact same data structure. temp table generated starting (and including) last day of items_moved table , finding what's new.

typical data looks like:

1, f:\snaps, pics, 6/10/2010, 366, 6531616131 2, f:\snaps, pics, 6/11/2010, 5, 464864 3, g:\pics, pics, 6/10/2010, 70, 123456667 4, g:\pics, pics, 6/11/2010, 56, 123645964

all i'm trying append temp table on main table that:

  1. there no duplicates (based on drive , date)
  2. if temp table has matching drive , date , larger values overwrites main table
  3. if row exists in temp table not main table, gets appended end of main .

the result in items_moved table records 1-1776 unchanged, end this:

1777, f:\snaps, pics, 6/09/2010, 66, 151616131         (unchanged) 1778, f:\snaps, pics, 6/10/2010, 366, 6531616131       (updated temp larger) 1779, g:\pics, pics, 6/09/2010, 58, 45645646           (unchanged) 1780, g:\pics, pics, 6/10/2010, 70, 123456667          (unchanged) 1781, f:\snaps, pics, 6/11/2010, 5, 464864             (added) 1782, g:\pics, pics, 6/11/2010, 56, 123645964          (added)

i've tried every variation on join can think of.

i can rows matching dates, can use update statement. can't rows temp don't have matching dates in items_moved table, can't run insert.

the final point execute part of vb action when user requests report access db.

here's snippet

insert items_moved (drive, type, file_date, file_count, file_size)   select 'f:\snaps','pics', temp_table.created, count(temp_table.created),   sum(temp_table.size) temp_table temp_table.drive = 'f:\snaps'    group temp_table.created

this works, adds end of table. i'm left problem of duplicates. i'm sure there's way de-duplicate table on fly, seems waste. i've found hints on using union , nothing on how use union update 1 of tables in union.

any input appreciated. thanks.

it seems match on drive, type , date, new records, perhaps:

insert items_moved (drive, type, file_date, file_count, file_size)  select t.drive, type, t.file_date, t.file_count, t.file_size  temp_table t left join items_moved m on t.drive = m.drive , t.type = m.type , t.file_date = m.file_date m.id null

a second query needed updates:

update items_moved  m inner join temp_table t on t.drive = m.drive , t.type = m.type , t.file_date = m.file_date set m.file_count = t.file_count, m.file_size = t.file_size m.file_size < t.file_size

or there abouts. mean?


Comments

Post a Comment

Popular posts from this blog

django - How can I change user group without delete record -

-
on website user have 1 group. , user can change group. it's made user.groups.clear() and user.groups.add(new_group) but it's not efficient, because there 2 sql query: delete, insert. how can change group update query? user , group related each other using manytomanyfield . means intersection table exists relating both entities, , if don't specify model map (using through attribute) django creates 1 you. looking @ sources django.contrib.auth.models see that's case. fortunatly, can access intermediary model using through attribute of manager (in case, user.groups.through ). can use regular model. example: >>> alice = user.objects.create_user('alice', 'alice@example.com', 'alicepw') >>> employee = group.objects.create(name='employee') >>> manager = group.objects.create(name='manager') >>> alice.groups.add(employee) >>> alice.groups.all() [<group: employee...
Read more

java - Need to add SOAP security token -

-
i need set custom soap header attribute on jax-ws generated webservice client. case webservice calls must go through proxy server requiring specific token (recieved web request header) present in soap request header. e.g.: 1 carserviceservice service = null; 2 service = new carserviceservice(new url(url), new qname(qname); 3 carserviceendpoint port = service.getcarserviceport(); it seems in line 3 wsdl retrieved , call fails due missing security token. 1 point direction on how done? a detailed example has been mentioned here: creating , deploying jax-ws web service on tomcat 6 this article shows how create , use security token .
Read more

java - EclipseLink JPA Object is not a known entity type -

-
i have strange problem eclipselink , object want persist. have 1 object (keypointlistimpl) stores object keypointimpl in list. persisting keypointimpl objects works great if try persist keypointlistimpl object java.lang.illegalargumentexception says object keypointimpl isn't known entity type. here keypointimpl code: @entity @table(name="keypoints") public class keypointimpl implements keypoint { @id @generatedvalue(strategy = generationtype.identity) private int id; @enumerated(enumtype.string) private detectortype keypointtype; private float x; private float y; private float size; private float angle; private float response; private int octave; private int classid; ... } here keypointlistimpl code: @entity @table(name="keypointlists") public class keypointlistimpl implements keypointlist { @id @generatedvalue(strategy = generationtype.identity) private int id; @onetoone(cascade={ca...
Read more