Question Update only 1st record out of multiple?

[tagah]

Can anyone help me on how update the 1st record out of the multiple. Look below..I have this data:

Order # Fee
20010 13
20010
20010
20011 13
20011
20012 13

I want my table to look like this. Is there anyway I can do this without updating the empty ones. I only want to update the first unique record.

Can anyone help me this.

 

[PNGBill]

Your table can't look like that.

You show 3 records 20010 & 2 with 20011

Each record should have a unique value in it's first field (normally) which is the Primary Key.

If you want to update just one record out of a number you need to think how this record differs from the others ??

Is it the only record on that date ?, First record when sorted by ID number ?, Last record when sorted ? and the options go on.

A select query will identify your record to update and then you change this to an Update Query and it will do the task you want - first, select the record to "prove" you are not going to turn your data into custard.

Before this can be done, either sort out your fields and or, supply more information on what you are dealing with.

 

[tagah]

THese are order # numbers...

 

[PNGBill]

THese are order # numbers...

?? Order Number repeated ??

You need to select the first record and then think about updating it.

VBA can appear to do this in one action but it will still need to know which record is the first (sort).

How do you sort records when they are the same??

You quite likely have other fields that for some reason you prefer to not share with the forum.

These "secret" fields may hold a clue to solving your problem.

 

[tagah]

OK Well

Here is an example.

Order # Product # Qty Fee
20012 CE0011 2
20012 CE0012 1
20012 CE0014 4
20013 CE0011 1
20013 CE0026 1

I want to only a fee to the first record of each order #. How does this help?

 

[PNGBill]

You have to "decide" the order of records for your order.

If the first record is so important, try to build into your Invoice an Inv Line number field.

Order/invoice detail would hold OrderNumber, LineNo, ProductCode, Qty etc.

You can then refer to this line number to get your "First" record.

Otherwise, however they are listed, any new view will list them in which ever way you choose. OrderBy Product Code, Qty, what ever.

Consider how the records are generated..

A normal way is to open a Header and then enter the Items in a subform.
The records are not "stored" in a subform, they are stored in a "transaction Table" Order records table, whatever..

Each of these records will have it's own Primary Key.

for the records selected by Order Number, Include the Primary key in your query and sort by this and you will have your correct order of entry - 1st record.

If you need help with the sql for this, confirm and I will make one up.

 

[ByteMyzer]

tagah,

Databases require an actual field/value to make this determination; unless you have a field in your table that tracks line numbers, the database has no way to track it for you.

What indicates the first record of each order number? Is it the Product #? Is there a Line Number? Example:
Order # Line # Product # Qty Fee
20012 1 CE0011 2
20012 2 CE0012 1
20012 3 CE0014 4
20013 1 CE0011 1
20013 2 CE0026 1

 

[PNGBill]

This sql will give you the First Record of each day - sorted by date ascending and for each OrderNumber sorted by OrderDetailID Ascending.

SELECT TblOrders.OrderNumber, TblOrders.OrderDate, First(TblOrderDetail.OrderDateilID) AS FirstOfOrderDateilID
FROM TblOrders INNER JOIN TblOrderDetail ON TblOrders.OrderNumber = TblOrderDetail.OrderID
GROUP BY TblOrders.OrderNumber, TblOrders.OrderDate
ORDER BY TblOrders.OrderDate, First(TblOrderDetail.OrderDateilID);

 

[tagah]

I have an Order Line which goes with the order #.

Order # Order Line

340639 191949
340639 191950
340463 191725
340463 191728
345082 197289
344073 196105

Cant I do sumtin like if the top record is less than the next one...make the top one equal to 13?

 

[PNGBill]

You want 191949 to be updated to 13 or a new field on this record to be updated to 13?

Either is possible as this record is the first for Order 340639

 

[PNGBill]

This query will make a temp table _TblOrdersTempTable

SELECT TblOrders.OrderNumber, First(TblOrderDetail.OrderLineID) AS FirstOfOrderLineID1 INTO _TblOrdersTempTable
FROM TblOrders INNER JOIN TblOrderDetail ON TblOrders.OrderNumber = TblOrderDetail.OrderID
GROUP BY TblOrders.OrderNumber
ORDER BY TblOrders.OrderNumber, First(TblOrderDetail.OrderLineID);

And this query will update a field on TblOrderDetail with "13" for each of the first line numbers of every order

UPDATE _TblOrdersTempTable INNER JOIN TblOrderDetail ON [_TblOrdersTempTable].FirstOfOrderLineID1 = TblOrderDetail.OrderLineID SET TblOrderDetail.OrderdetailNewField = 13;

 

[PNGBill]

In my Sample Data I have the Order Detail Line Number as the Primary Key for the Order Detail Table. This means I can Not change that Number (191949) to 13 as then you would have Duplicate Primary Keys.

If this is your intention to change this number then explain what that number is ? If it is like my example, the PK then you shouldn't change it or even can't.

If it is another number, say product number, then why change it.

Anyway, queries I gave you will make a different field "13" for the first records of each order.

 

[ByteMyzer]

Cant I do sumtin like if the top record is less than the next one...make the top one equal to 13?

Try something like the following (substitute bracketed table/field names with the actual names):

UPDATE [B][I][MyTable][/I][/B] T1
SET T1.[B][I][Fee][/I][/B] = 13
WHERE T1.[B][I][Line_No][/I][/B] = (
	SELECT MIN(T2.[B][I][Line_No][/I][/B])
	FROM [B][I][MyTable][/I][/B] T2
	WHERE T2.[B][I][Order_No][/I][/B] = T1.[B][I][Order_No][/I][/B]);

 

[tagah]

Thanks ByteMyzer, that worked like a charm!!

 

[ByteMyzer]

You're welcome; I'm glad it worked for you.