Solved

Update Distinct Values

Posted on 2006-06-12
13
441 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
  • 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 250 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 65

Assisted Solution

by:rockiroads
rockiroads earned 250 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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

685 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