I'd suggest creating a separate table with columns PartNumberSpec and
PartNumber. In the first column put the specified part number , Part A in
your example and in the second column the part you want to show in the
report, Part B in your example.
You'll firstly need to fill the new table with al the part numbers, which
you can do with a simple 'append' query:
INSERT INTO [NewTable]([PartNumberSpec])
SELECT DISTINCT [PartNumber]
FROM [ExistingTable]
ORDER BY [PartNumber];
The insert the new part numbers you want to see in place of the originals in
the PartNumber column of the relevant rows of the new table. Next you need
to fill the rows with empty part numbers in the new table with the original
values, which you can do with a simple 'update' query:
UPDATE [NewTable]
SET [PartNumber] = [PartNumberSpec]
WHERE [PartNumber] IS NULL;
You can then base your report on a query which joins the new table to the
existing table on the PartNumberSpec coulumn from the new table equalling the
part number in the existing table. In the query return the PartNumber column
from the new table, not that from the existing table.
By using a table to make the replacements like this it is maintainable
simply by editing the data in the table rather than having to amend the
report (or its underlying query's) definition if, for instance, you need to
substitute Part C for part A instead of Part B. The replacement part numbers
are data, and it’s not a good idea to hard-code data in expressions etc. In
fact it goes against one of the most fundamental principles of the database
relational model, the 'information principle', which requires data to be
stored as explicit values at column positions in rows in tables, and in no
other way.
If you want to revert to showing all the existing parts in the report then
you simply have to update the new table with:
UPDATE [NewTable]
SET [PartNumber] = [PartNumberSpec];
Ken Sheridan
Stafford, England
