help to avoid repeated or duplicate field

[eddii]

i have a query which contains two tables linked with table1 and table2

table1 ( table name )
suppliers( field name ) months( another field name in table1 )
asd101xyz jan
asd101xyz jul
asd102xyz jan
asd103xyz jan

table2 ( table name )
suppliers( field name )
asd101xyz
asd102xyz
asd103xyz

both are linked as join properties "suppliers"

few records are repeated because some records have same number but "months " field value is different in table1 i dont want the suppliers to sort on suppliers and months filed so that the record will not duplicate or repeat

can any one help me how to do

 

[vbaInet]

Can you show us in a spreadsheet or on here sample records of:

1. Your current set of data when joined
2. Your expected output.

 

[plog]

Not without examples and SQL.

Provide sample data from your table, the SQL of your query, what the output query is returning based on that sample data and what you want the query to return based on that sample data.

 

[eddii]

thanks for the reply i will upload my db tomorrow as iam out of office

thankyou

i have a query which contains two tables linked with table1 and table2

table1 ( table name )
suppliers( field name ) months( another field name in table1 )
asd101xyz jan
asd101xyz jul
asd102xyz jan
asd103xyz jan

table2 ( table name )
suppliers( field name )
asd101xyz
asd102xyz
asd103xyz

both are linked as join properties "suppliers"

few records are repeated because some records have same number but "months " field value is different in table1 i dont want the suppliers to sort on suppliers and months filed so that the record will not duplicate or repeat

can any one help me how to do V

 

[eddii]

have a query which contains two tables linked with table1 and table2

imte master ( table name )
IMTE NUMBER( field name ) monthofcal( another field name in table1 )
asd101xyz jan
asd101xyz jul
asd102xyz jan
asd103xyz jan

CALIBRATION_CERTIFICATE ( table name )
IMTE NUMBER( field name )
asd101xyz
asd102xyz
asd103xyz

both are linked as join properties "IMTE NUMBER"

few records are repeated because some records have same number but "monthofcal " field value is different in table1,. i want the imte number to sort on imte number and monthofcal filed so that the record will not duplicate or repeat

http://www.dbforums.com/#sql view

SELECT [IMTE MASTER].[IMTE NUMBER], CALIBRATION_CERTIFICATE.[IMTE NUMBER], [IMTE MASTER].monthofcal, CALIBRATION_CERTIFICATE.STATUSCER
FROM [IMTE MASTER] LEFT JOIN CALIBRATION_CERTIFICATE ON [IMTE MASTER].[IMTE NUMBER] = CALIBRATION_CERTIFICATE.[IMTE NUMBER];


can any one help me how to do

 

[vbaInet]

Why the repetition?

 

[eddii]

due to sql added in second reply

 

[vbaInet]

Ok, you've shown us the SQL but we also want the following -->

Can you show us in a spreadsheet or on here sample records of:

1. Your current set of data when joined
2. Your expected output.

 

[eddii]

in the attached database Q09427F51 is repeating twice if i select in reportform

plant (p1)
shop (qd)
month(jan)

it should show only jan value
(in table calibraiton certificate the next cal due date) should be

and if i select
plant (p1)
shop (qd)
month(jul)
it should show only jul value

 

[vbaInet]

What form or report should I be looking at?

 

[eddii]

if you open the database

open reportform

select :
p1
qd
jan

and click the button calibration month status

then in the report form Q09427f51 appear twice it should appear only once because for this record in calibration certificate table there are two records in which

next cal due date field which contains date 30/Jul/2011 and
should appear in report if i select this conbination
p1
qd
jan
and report no ( rep no = 83 )

next cal due date field which contains date 01/Jan/2012
should appear in report if i select this conbination

p1
qd
jul

and report no ( rep no = 5005 )

the problem hapenning is if i select "jan" both reports ( rep no : 5005 & 83 are appearing
same repeats if i select
p1
qd
jul

rep no is not in report view

 

[vbaInet]

So the only problem is the repetition of rows? Change DISTINCTROW to DISTINCT.