Hi, How do we avoid repeated records?

[radicrains]

Hi, I have 3 queries that contains different information. And i want to combine them so that my Report will have a common "drawingNo", "Title" and 2 sets of data each from different queries..

When i run the queries, there will be repeated records.. Is there a way to avoid this? may i know how?

thanks..

[Rixxe]

Select distinct a.drawingNo, a.title
from tblname as a

Or depending on what the fields are (and what other fields you have) you might want to use group by...

Select a.drawingNo, a.title
from tblname as a
Group by a.drawingNo

[radicrains]

Select distinct a.drawingNo, a.title
from tblname as a

Or depending on what the fields are (and what other fields you have) you might want to use group by...

Select a.drawingNo, a.title
from tblname as a
Group by a.drawingNo

Thanks, however... erm, I'm still new to Access... Is "a" Refering to the Query's name that i store in?

And, am i suppose to type this at SQL or in the criteria of the query? If it's at SQL, then where should i write it? union query?

thanks...

[Rixxe]

Sorry i may have misunderstood,

Do you have 2 tables?

The 'a' represents the table name, but you dont need to use it, instead just use the table name:

 
SELECT DISTINCT tblname.drawingNo, tblname.title
fFROM tblname

If you are using the design view, there should be a button to show 'SQL View', type the code in there.

Replace 'tblName' with the name of your table (if its only 1) If it is infact 2 tables, then we can change the query.

If you have any more information, please provide.

Good luck.

[Rixxe]

This is also a really good site if you want to learn via a tutorial.

It also provides fairly basic instructions on how to use 'Distinct' and 'Group by'


http://www.w3schools.com/sql/

Good luck

[radicrains]

Sorry i may have misunderstood,

Do you have 2 tables?

The 'a' represents the table name, but you dont need to use it, instead just use the table name:

 
SELECT DISTINCT tblname.drawingNo, tblname.title
fFROM tblname

If you are using the design view, there should be a button to show 'SQL View', type the code in there.

Replace 'tblName' with the name of your table (if its only 1) If it is infact 2 tables, then we can change the query.

If you have any more information, please provide.

Good luck.

hi! sry for the late reply. been trying to fig it out.. anyway, here's more infor on the proj that i need to do.. www.accessforums/access/please-help-me-out-union-query-8975.html

i've post many post on this particular qn cause i'm rushing thru my dateline and i'm still kinda stuck.. anyway, another forumer posted in my other thread that i need to use SELECT DISTINCT ROWS instead, and when i read it up, kinda gotten lost.. may i know, what's is inner join for?
thanks in adv..

[radicrains]

https://www.accessforums.net/access/...uery-8975.html

Sry, here's the actual link..

thanks for the webby you gave to me too!

[Rixxe]

Well firstly if you are not sure what something is (Like an inner join) please just google it.
There are a million and 1 sites that can help you understand SQL, and meanings.

Inner joins return all rows from multiple tables where the join condition is met.

And..

The UNION query allows you to combine the result sets of 2 or more "select" queries. It removes duplicate rows between the various "select" statements.
Each SQL statement within the UNION query must have the same number of fields in the result sets with similar data types.
The syntax for a UNION query is:

select field1, field2, . field_n
from tables
UNION
select field1, field2, . field_n
from tables;

All this formation was taken from http://www.techonthenet.com/sql/union.php

None the less, regarding your question. Yes you can use Select Distinct Rows instead.

Reading up on all this is the best way to make a query you want.

If you do want help on how to make the query work, please post all your table names, and field names.... But please have a go first.

[radicrains]

alrights.. i'll play around with it tonight... if i'm still unable to solve by tmr, i'll post my work/fields back to you.. thanks..

[radicrains]

oh yes, if u want a look at my table fields, it's available at the website i've link it to you earlier today... it explains much clearer with pictures of end product...

thank you for being patient with me.. really appreciate it..

[radicrains]

Anyways, I've managed to figured out over the weekends after doing lots of reading.. THANKS! May God bless you!

[Rixxe]

Well done, Knew you could do it.