Other form of CTRL+APOSTROPHE (')

[DanBakerUSAF]

I have a spreadsheet 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.

 

[Allen Browne]

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

 

[DanBakerUSAF]

I have looked at your subquery page and am still a bit lost as to where the
below statement needs to go.

I may have it completely wrong but this is what I am trying and it is
generating an error;

SELECT [Availability Report].ID, [Availability Report].[NSN/MODDACC],
[Availability Report].Location, [Availability Report].Description,
[Availability Report].QTY, [Availability Report].UOM, [Availability
Report].[LOT/Serial#], [Availability Report].Cost, [Availability
Report].Status
(UPDATE [Availability Report]
SET [NSN/MODDACC] =
(SELECT First( [NSN/MODDACC]) AS NewPartNum
FROM [Availability Report] AS Dupe
WHERE ((Dupe.ID < Table1.ID)
AND (Dupe. [NSN/MODDACC] Is Not Null))
ORDER BY Dupe.ID DESC)
WHERE [NSN/MODDACC] Is Null)
FROM [Availability Report];

Any thoughts??



I am giving these since I was a little confused with the Dupe reference.

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

Reply to group, rather than allenbrowne at mvps dot org.

I have a spreadsheet 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.

 

[DanBakerUSAF]

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?

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

Reply to group, rather than allenbrowne at mvps dot org.

I have a spreadsheet 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.

 

[Allen Browne]

Do you have a primary key field named ID in the [Availability Report] table?

You need it for this to work.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

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?

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

Reply to group, rather than allenbrowne at mvps dot org.

I have a spreadsheet 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.

 

[DanBakerUSAF]

Yes, it's the only way I keep the table straight right now.

Do you have a primary key field named ID in the [Availability Report] table?

You need it for this to work.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

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?

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

Reply to group, rather than allenbrowne at mvps dot org.

I have a spreadsheet 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.

 

[Allen Browne]

Then I don't understand what's wrong.

Perhaps it doesn't like the First(). Try:
UPDATE Table1
SET [Part#] =
(SELECT TOP 1 [Part#]
FROM Table1 AS Dupe
WHERE ((Dupe.ID < Table1.ID)
AND (Dupe.[Part#] Is Not Null))
ORDER BY Dupe.ID DESC)
WHERE [Part#] Is Null;

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Yes, it's the only way I keep the table straight right now.

Do you have a primary key field named ID in the [Availability Report]
table?

You need it for this to work.

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?


:

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

Reply to group, rather than allenbrowne at mvps dot org.

message
I have a spreadsheet 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.

 

[John Spencer]

STEP 1: BACKUP your data before attempting the following.
STEP 2: BACKUP your data before attempting the following.

Without a backup you cannot restore the data if this does not work the way you
expect.

See if the following query gives you the desired result.

SELECT ID,
(SELECT Max([Part#])
FROM [YourTable] AS B
WHERE B.ID <= A.ID
AND B.[Part#] = A.[Part#]) as ThePart
FROM [YourTable] as A

IF it does then turn it into a make table query or populate an already
designed table with two fields ID (Number Type Long) and ThePart (text field).

The make table query would look like:

SELECT ID,
(SELECT Max([Part#])
FROM [YourTable] AS B
WHERE B.ID <= A.ID
AND B.[Part#] = A.[Part#]) as ThePart
INTO [tblFixPartNo]
FROM [YourTable] as A

NOW update your original table
UPDATE [YourTable] INNER JOIN [tblFixPartNo]
ON [YourTable].[ID] = [tblFixPartNo].[ID]
SET [YourTable].[Part#] = [tblFixPartNo].[ThePart]
WHERE [YourTable].[Part#] is Null or
[YourTable].[Part#] = ""

Another alternative would be to link to the Excel sheet and then use VBA to
step through it a line at a time and create new records into an Access table.
You would need to store the last Part# from the Excel sheet into a variable
and use the variable to populate the field.

This second approach will be notably less efficient than the series of queries
and may cause bloat in your database.


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County