do not show duplicates

D

Dennis Villareal

ok in my inventory we have a bunch of product made from the same batch and i
might have 20 total of one item, and all the information is the same. is
there a way to remove duplicates. so instead of 20 total i will show 1 of
each batch?

is there something easy i can put in the Criteria of the query to eliminate
duplicates
 
J

Jeff Boyce

Dennis

"How" depends on "what"...

If we don't know your data structure, and if we don't have the SQL
statement, it will be a bit tough to offer "how" ideas...

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
D

Dennis Villareal

the data is pulling from a text field in a locked table. i cannot edit the
table at all.

Heat: PRD_HET_NO <-- this is the field i want to show no dups. i would need
to remove some of the other fields like Tag: PRD_TAG_NO & LGT:
([PRD_LGTH]/12).

when i goto the sql veiw in the query this is what is displayed

SELECT [ESINDL01_INTPRD_REC - TAGS IN SYSTEM].PRD_TAG_NO AS Tag,
[ESINDL01_INTPRD_REC - TAGS IN SYSTEM].PRD_GRD AS GRD, ([PRD_LGTH]/12) AS
LGT,
Mid([PRD_SPEC_FLDS_1],InStr([PRD_SPEC_FLDS_1],"YLD=")+4,InStr([PRD_SPEC_FLDS_1],"/TEN")-5)
AS YLD,
Mid([PRD_SPEC_FLDS_1],InStr([PRD_SPEC_FLDS_1],"/TEN=")+5,InStr([PRD_SPEC_FLDS_1],"/ELG")-(InStr([PRD_SPEC_FLDS_1],"/TEN=")+5))
AS TEN, Mid([PRD_SPEC_FLDS_1],InStr([PRD_SPEC_FLDS_1],"/ELG=")+5) AS ELG,
[ESINDL01_INRPRM_REC - Product Master].PRM_SIZE_DESC AS [SIZE],
[ESINDL01_INTPRD_REC - TAGS IN SYSTEM].PRD_MIL AS Mill, [ESINDL01_INTPRD_REC
- TAGS IN SYSTEM].PRD_HET_NO AS Heat, [ESINDL01_INTPRD_REC - TAGS IN
SYSTEM].PRD_CRTD_DT
FROM [ESINDL01_INRPRM_REC - Product Master] INNER JOIN ([ESINDL01_INTRES_REC
- TAGS RESERVED TO ORDERS] INNER JOIN [ESINDL01_INTPRD_REC - TAGS IN SYSTEM]
ON ([ESINDL01_INTRES_REC - TAGS RESERVED TO ORDERS].RES_GRD =
[ESINDL01_INTPRD_REC - TAGS IN SYSTEM].PRD_GRD) AND ([ESINDL01_INTRES_REC -
TAGS RESERVED TO ORDERS].RES_SIZE = [ESINDL01_INTPRD_REC - TAGS IN
SYSTEM].PRD_SIZE) AND ([ESINDL01_INTRES_REC - TAGS RESERVED TO
ORDERS].RES_TAG_NO = [ESINDL01_INTPRD_REC - TAGS IN SYSTEM].PRD_TAG_NO)) ON
([ESINDL01_INRPRM_REC - Product Master].PRM_SIZE = [ESINDL01_INTPRD_REC -
TAGS IN SYSTEM].PRD_SIZE) AND ([ESINDL01_INRPRM_REC - Product Master].PRM_GRD
= [ESINDL01_INTPRD_REC - TAGS IN SYSTEM].PRD_GRD)
WHERE ((([ESINDL01_INTPRD_REC - TAGS IN SYSTEM].PRD_TAG_NO) Not Like ".*"
And ([ESINDL01_INTPRD_REC - TAGS IN SYSTEM].PRD_TAG_NO) Not Like "Z*") AND
(([ESINDL01_INTPRD_REC - TAGS IN SYSTEM].PRD_GRD)="AX") AND
(([ESINDL01_INTPRD_REC - TAGS IN SYSTEM].PRD_CRTD_DT) Like "2008*"));

basicly all the information is the same except the Tag: PRD_TAG_NO for most
of them.

let me know if you need anything else
 
J

Jeff Boyce

I'm going to guess that you are seeing what you consider "duplicates"
because you are joining tables that have a one-to-many relationship.

One approach to seeing only "unique" values is to open the query in design
view, open the properties window, and set Unique Values to "yes".

Regards

Jeff Boyce
Microsoft Office/Access MVP


Dennis Villareal said:
the data is pulling from a text field in a locked table. i cannot edit
the
table at all.

Heat: PRD_HET_NO <-- this is the field i want to show no dups. i would
need
to remove some of the other fields like Tag: PRD_TAG_NO & LGT:
([PRD_LGTH]/12).

when i goto the sql veiw in the query this is what is displayed

SELECT [ESINDL01_INTPRD_REC - TAGS IN SYSTEM].PRD_TAG_NO AS Tag,
[ESINDL01_INTPRD_REC - TAGS IN SYSTEM].PRD_GRD AS GRD, ([PRD_LGTH]/12) AS
LGT,
Mid([PRD_SPEC_FLDS_1],InStr([PRD_SPEC_FLDS_1],"YLD=")+4,InStr([PRD_SPEC_FLDS_1],"/TEN")-5)
AS YLD,
Mid([PRD_SPEC_FLDS_1],InStr([PRD_SPEC_FLDS_1],"/TEN=")+5,InStr([PRD_SPEC_FLDS_1],"/ELG")-(InStr([PRD_SPEC_FLDS_1],"/TEN=")+5))
AS TEN, Mid([PRD_SPEC_FLDS_1],InStr([PRD_SPEC_FLDS_1],"/ELG=")+5) AS ELG,
[ESINDL01_INRPRM_REC - Product Master].PRM_SIZE_DESC AS [SIZE],
[ESINDL01_INTPRD_REC - TAGS IN SYSTEM].PRD_MIL AS Mill,
[ESINDL01_INTPRD_REC
- TAGS IN SYSTEM].PRD_HET_NO AS Heat, [ESINDL01_INTPRD_REC - TAGS IN
SYSTEM].PRD_CRTD_DT
FROM [ESINDL01_INRPRM_REC - Product Master] INNER JOIN
([ESINDL01_INTRES_REC
- TAGS RESERVED TO ORDERS] INNER JOIN [ESINDL01_INTPRD_REC - TAGS IN
SYSTEM]
ON ([ESINDL01_INTRES_REC - TAGS RESERVED TO ORDERS].RES_GRD =
[ESINDL01_INTPRD_REC - TAGS IN SYSTEM].PRD_GRD) AND
([ESINDL01_INTRES_REC -
TAGS RESERVED TO ORDERS].RES_SIZE = [ESINDL01_INTPRD_REC - TAGS IN
SYSTEM].PRD_SIZE) AND ([ESINDL01_INTRES_REC - TAGS RESERVED TO
ORDERS].RES_TAG_NO = [ESINDL01_INTPRD_REC - TAGS IN SYSTEM].PRD_TAG_NO))
ON
([ESINDL01_INRPRM_REC - Product Master].PRM_SIZE = [ESINDL01_INTPRD_REC -
TAGS IN SYSTEM].PRD_SIZE) AND ([ESINDL01_INRPRM_REC - Product
Master].PRM_GRD
= [ESINDL01_INTPRD_REC - TAGS IN SYSTEM].PRD_GRD)
WHERE ((([ESINDL01_INTPRD_REC - TAGS IN SYSTEM].PRD_TAG_NO) Not Like ".*"
And ([ESINDL01_INTPRD_REC - TAGS IN SYSTEM].PRD_TAG_NO) Not Like "Z*") AND
(([ESINDL01_INTPRD_REC - TAGS IN SYSTEM].PRD_GRD)="AX") AND
(([ESINDL01_INTPRD_REC - TAGS IN SYSTEM].PRD_CRTD_DT) Like "2008*"));

basicly all the information is the same except the Tag: PRD_TAG_NO for
most
of them.

let me know if you need anything else

Jeff Boyce said:
Dennis

"How" depends on "what"...

If we don't know your data structure, and if we don't have the SQL
statement, it will be a bit tough to offer "how" ideas...

Regards

Jeff Boyce
Microsoft Office/Access MVP
Click to expand...
Click to expand...
 
D

Dennis Villareal

thanks jeff that was very easy and i will be using that in alot of my
datadase queries. you just saved me days and days of work. thanks alot!!



Jeff Boyce said:
I'm going to guess that you are seeing what you consider "duplicates"
because you are joining tables that have a one-to-many relationship.

One approach to seeing only "unique" values is to open the query in design
view, open the properties window, and set Unique Values to "yes".

Regards

Jeff Boyce
Microsoft Office/Access MVP


Dennis Villareal said:
the data is pulling from a text field in a locked table. i cannot edit
the
table at all.

Heat: PRD_HET_NO <-- this is the field i want to show no dups. i would
need
to remove some of the other fields like Tag: PRD_TAG_NO & LGT:
([PRD_LGTH]/12).

when i goto the sql veiw in the query this is what is displayed

SELECT [ESINDL01_INTPRD_REC - TAGS IN SYSTEM].PRD_TAG_NO AS Tag,
[ESINDL01_INTPRD_REC - TAGS IN SYSTEM].PRD_GRD AS GRD, ([PRD_LGTH]/12) AS
LGT,
Mid([PRD_SPEC_FLDS_1],InStr([PRD_SPEC_FLDS_1],"YLD=")+4,InStr([PRD_SPEC_FLDS_1],"/TEN")-5)
AS YLD,
Mid([PRD_SPEC_FLDS_1],InStr([PRD_SPEC_FLDS_1],"/TEN=")+5,InStr([PRD_SPEC_FLDS_1],"/ELG")-(InStr([PRD_SPEC_FLDS_1],"/TEN=")+5))
AS TEN, Mid([PRD_SPEC_FLDS_1],InStr([PRD_SPEC_FLDS_1],"/ELG=")+5) AS ELG,
[ESINDL01_INRPRM_REC - Product Master].PRM_SIZE_DESC AS [SIZE],
[ESINDL01_INTPRD_REC - TAGS IN SYSTEM].PRD_MIL AS Mill,
[ESINDL01_INTPRD_REC
- TAGS IN SYSTEM].PRD_HET_NO AS Heat, [ESINDL01_INTPRD_REC - TAGS IN
SYSTEM].PRD_CRTD_DT
FROM [ESINDL01_INRPRM_REC - Product Master] INNER JOIN
([ESINDL01_INTRES_REC
- TAGS RESERVED TO ORDERS] INNER JOIN [ESINDL01_INTPRD_REC - TAGS IN
SYSTEM]
ON ([ESINDL01_INTRES_REC - TAGS RESERVED TO ORDERS].RES_GRD =
[ESINDL01_INTPRD_REC - TAGS IN SYSTEM].PRD_GRD) AND
([ESINDL01_INTRES_REC -
TAGS RESERVED TO ORDERS].RES_SIZE = [ESINDL01_INTPRD_REC - TAGS IN
SYSTEM].PRD_SIZE) AND ([ESINDL01_INTRES_REC - TAGS RESERVED TO
ORDERS].RES_TAG_NO = [ESINDL01_INTPRD_REC - TAGS IN SYSTEM].PRD_TAG_NO))
ON
([ESINDL01_INRPRM_REC - Product Master].PRM_SIZE = [ESINDL01_INTPRD_REC -
TAGS IN SYSTEM].PRD_SIZE) AND ([ESINDL01_INRPRM_REC - Product
Master].PRM_GRD
= [ESINDL01_INTPRD_REC - TAGS IN SYSTEM].PRD_GRD)
WHERE ((([ESINDL01_INTPRD_REC - TAGS IN SYSTEM].PRD_TAG_NO) Not Like ".*"
And ([ESINDL01_INTPRD_REC - TAGS IN SYSTEM].PRD_TAG_NO) Not Like "Z*") AND
(([ESINDL01_INTPRD_REC - TAGS IN SYSTEM].PRD_GRD)="AX") AND
(([ESINDL01_INTPRD_REC - TAGS IN SYSTEM].PRD_CRTD_DT) Like "2008*"));

basicly all the information is the same except the Tag: PRD_TAG_NO for
most
of them.

let me know if you need anything else

Jeff Boyce said:
Dennis

"How" depends on "what"...

If we don't know your data structure, and if we don't have the SQL
statement, it will be a bit tough to offer "how" ideas...

Regards

Jeff Boyce
Microsoft Office/Access MVP

message ok in my inventory we have a bunch of product made from the same batch
and
i
might have 20 total of one item, and all the information is the same.
is
there a way to remove duplicates. so instead of 20 total i will show 1
of
each batch?

is there something easy i can put in the Criteria of the query to
eliminate
duplicates
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Duplicates in union query 3
Counting number of items excluding duplicates. 1
Cumulative formula for word table 0
Global layouts 0
Duplicates Deletion ? 2
Access Lock File .LACCDB / Now shows as a notepad file 0
Remove checked items from To Do lists 0
Query Problem (Duplicates) 2

Top