Solved

Update Distinct Values

Posted on 2006-06-12
13
447 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 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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your 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

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

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

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
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…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

751 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