Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Update Distinct Values

Posted on 2006-06-12
13
Medium Priority
?
466 Views
Last Modified: 2008-02-01
I have the folowing Query that selects distinct values from a temp table and creates a new table.

Select distinct [LFILE_temp].[mfo], max([LFILE].boxes), INTO [LFILE_main]
FROM [LFILE_temp]
GROUP BY [LFILE_temp].[mfo]

How can i turn this query in to an update query so the previous data in LFILE_main isnt being replaced every time the query is run?

Thanks,
SM
0
Comment
Question by:seanmorris
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
  • 3
13 Comments
 
LVL 65

Expert Comment

by:rockiroads
ID: 16884358
What do u mean the previous data?

Do u want to insert a new record or update an existing record, if u want to update a existing record, which one do u want to update and to what?

0
 

Author Comment

by:seanmorris
ID: 16884374
Sorry,

I want to insert the distinct values from the temp table into the main table, so eventually over time i'll have a table full of distinct values

Thanks
SM
0
 
LVL 77

Accepted Solution

by:
peter57r earned 1000 total points
ID: 16884494
Hi seanmorris,
You use an append query:

Insert  INTO [LFILE_main]
Select  [LFILE_temp].[mfo], max([LFILE].boxes) FROM [LFILE_temp]
 GROUP BY [LFILE_temp].[mfo]

There is no point in using Distinct with Group By here.


Pete
0
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
LVL 65

Assisted Solution

by:rockiroads
rockiroads earned 1000 total points
ID: 16884502
try this then


insert into lfile_main (mfo, boxes) select mfo, boxes from lfile_temp


quick explanation

insert into <<main table>> (<<columns in main table>>) select <<columns from temp table>> from <<temp table>>


Note the order and number of columns must match the insert and the select


If u want to record when it was created, u could always add a date in there as well

insert into lfile_main (mfo, boxes, createddate) select mfo, boxes, now() from lfile_temp

also u should ensure lfile_main has a unique key, e.g. ID of type autonumber, this does not need to be put into the SQL
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 16884550
hmmmm, I forgot to wrap the max, sorry

insert into lfile_main (mfo, boxes) select mfo, max(boxes) from lfile_temp group by mfo


but then Pete has already done that!

0
 

Author Comment

by:seanmorris
ID: 16884668
I'm getting an error with the aggregates.

Its because in the temp file there may be multiple records with the same field (MFO) hence the reason for using distinct and taking the Max of (Boxes) with the MFO.

Is there any way of using distinct to take every unique MFO record with Max of Boxes?

Thanks
Sean?
0
 
LVL 77

Expert Comment

by:peter57r
ID: 16884756
'Is there any way of using distinct to take every unique MFO record with Max of Boxes? '

Group By already does this.
Remove the Distinct.

Pete
0
 

Author Comment

by:seanmorris
ID: 16884767
I know this works, but it overwrites the existing table.

Select distinct [LFILE_temp].[mfo], max([LFILE].boxes), INTO [LFILE_main]
FROM [LFILE_temp]
GROUP BY [LFILE_temp].[mfo]

Would another way to be use my distinct query to create a buffer table and then use the insert query you guys suggested to update my main table?

SM
0
 

Author Comment

by:seanmorris
ID: 16884831
Pete

Sorry i would want to insert more than one record at a time because the table has multiple different MFO's along with some duplicates with different boxes.
e.g.

MFO-Number      Boxes      
146752            4970
146752            4970
146752            5000
146755            10488
146756            9758
146757            5101
146766                      15228
146766            16000
146766                      15228


 Hence using max([LFILE].boxes), in the statement only allows me to update one record into my main table.

SM
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 16884836
SM, u have to do it the

INSERT INTO way

how many columns do u have in lfile_main? if the number and order of columns match, u can just do a straight insert into table select like Pete as suggested, otherwise you have to specify the columns

insert into lfile_main (mfo, boxes) select mfo, max(boxes) from lfile_temp group by mfo
0
 
LVL 77

Expert Comment

by:peter57r
ID: 16884845
So have you actually triied using what I posted?

Pete
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 16884861
based on your data

select mfo, max(boxes) from lfile_temp group by mfo


will return

146752          5000
146755          10488
146756          9758
146757          5101
146766          16000


so running a insert into .... should insert those records



0
 

Author Comment

by:seanmorris
ID: 16884916
Pete,

My apologies, yes it does work i must have had a field not matching up properly.

This is exactly what i was looking for

Cheers

SM
0

Featured Post

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

704 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question