Re: Other form of CTRL+APOSTROPHE (')

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

My previous post was confusing. What I have done is setup an Update query
with criteria set to 'is null' for the field 'NSN/MODDACC'. The update to
field is as follows:

(SELECT First([NSN/MODDACC]) AS NewPartNum
FROM [Availability Report] AS Dupe
WHERE ((Dupe.ID < [Availability Report].ID)
AND (Dupe.[NSN/MODDACC] Is Not Null))
ORDER BY Dupe.ID DESC)

It has a problem with Dupe.ID
What part is wrong? What do I not understand?


"Allen Browne" wrote:

Provided you have a primary key field in the table so that the sorting order
is defined, you can execute an Update query that contains a subquery that
gets the most recent non-null Part# for each row.

This example assumes a table named Table1, with an Autonumber named ID to
define the sort order:

UPDATE Table1
SET [Part#] =
(SELECT First([Part#]) AS NewPartNum
FROM Table1 AS Dupe
WHERE ((Dupe.ID < Table1.ID)
AND (Dupe.[Part#] Is Not Null))
ORDER BY Dupe.ID DESC)
WHERE [Part#] Is Null;

If subqueries are new, here's an introduction:
http://allenbrowne.com/subquery-01.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"DanBakerUSAF" <DanBakerUSAF@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:7C49037A-E833-47A2-87D7-B7EB1A28BBB4@xxxxxxxxxxxxxxxx
I have a spread*** I am importing and removing unused field and records
automatically. The trouble is my part # field needs to be repeated to
blank
fields on the records below. I could use the CTRL ' but this needs to be
done 2-3 times a week and there are over 11,500 records imported each
time.

Example of current table view:

Part# Desc QTY UOM
5820 ANT
1 EA
5 EA
9685 BEE
7 EA
3 EA
What I need:
Part# Desc QTY UOM
5820 ANT
5820 1 EA
5820 5 EA
9685 BEE
9685 7 EA
9685 3 EA

This way I could then create a new table for QTY, UOM and a sperate one
for
Part# and Desc using the Part# for a relationship. There are many more
fields involved but the part# is the one I need repeated and only repeated
until the next part number in the record, then that one would be repeated.

Anyway this could be done via code, function or whatever either in Access
or
Excel would do.


.

Quantcast