Solved

Update Distinct Values

Posted on 2006-06-12
13
402 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
 
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

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…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

707 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now