Solved

Update Distinct Values

Posted on 2006-06-12
13
423 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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Documents and settings folder 30 76
backup programme - VBA 3 24
Binding recordsets to a form 6 22
Filter a form 8 12
When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

786 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