By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,073 Members | 1,218 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,073 IT Pros & Developers. It's quick & easy.

joining queries that return different nos. of records

P: 30
Hi!

I have a problem in my query... I want to join my two tables with the same date and returns in different records but it returns duplicate record..

Here is the examples:
Table1:
Field1 Field2 Field 3
Cynthia 3/22/07 25
Cynthia 3/23/07 26
Cynthia 3/24/07 27

Table2:
Field1 Field2 Field3
Cynthia 3/18/07 26
Cynthia 3/23/07 35
Cynthia 3/24/07 37

I want to show all results for the 4 fields in one query without any duplicates. But when I try to combine these two queries this is the result:

QueryTable
Field1 Field2 Field3 Field4
Cynthia 3/22/07 25
Cynthia 3/23/07 26
Cynthia 3/23/07 26 35
Cynthia 3/24/07 27 37
Cynthia 3/24/07 27
Cynthia 3/22/07 25

But the result I want is this:
Query Table:
Field1 Field2 Field3 Field4
Cynthia 3/22/07 25
Cynthia 3/23/07 26 35
Cynthia 3/24/07 27 37

Does anyone know how to do this? PLs. help me..... I need this result for my report in visual basic 6.0..
tnx....
Jan 24 '08 #1
Share this Question
Share on Google+
6 Replies


MindBender77
100+
P: 234
Hi!

I have a problem in my query... I want to join my two tables with the same date and returns in different records but it returns duplicate record..
Could you give the SQL for the query you are using? Copy the SQL from the SQL view of the query designer.

I'm not understanding how you're getting a 4th field in the result table if you only have 3 fields in each table.

JS
Jan 24 '08 #2

P: 30
Could you give the SQL for the query you are using? Copy the SQL from the SQL view of the query designer.

I'm not understanding how you're getting a 4th field in the result table if you only have 3 fields in each table.

JS
I'm sorry, The field4 comes from the value of Field3 in the table2 and what i want to do is to join it together with the same date and name in my table1 as one query table.

here is my SQL

SELECT DISTINCT Table1.Employee, IIf(Table1!Date=Table2!Edate,Table2!EDate,"") AS EDate1, Table1.Date, Table1.ValueID
FROM Table2, Table1;

Thank you.......
Jan 24 '08 #3

MindBender77
100+
P: 234
I'm sorry, The field4 comes from the value of Field3 in the table2 and what i want to do is to join it together with the same date and name in my table1 as one query table.

here is my SQL

SELECT DISTINCT Table1.Employee, IIf(Table1!Date=Table2!Edate,Table2!EDate,"") AS EDate1, Table1.Date, Table1.ValueID
FROM Table2, Table1;

Thank you.......
Interesting..... Try this:
Expand|Select|Wrap|Line Numbers
  1. SELECT test_table1.Name, test_table1.Date1, test_table1.[Amount1], test_tablee2.[Amount2]
  2. FROM test_table1 INNER JOIN test_tablee2 ON (test_table1.Name = test_tablee2.Name) AND (test_table1.Date1 = test_tablee2.Date1)
  3. GROUP BY test_table1.Name, test_table1.Date1, test_table1.[Amount Paid], test_tablee2.[Amount Paid]
  4. ORDER BY test_table1.Date1;
  5.  
I think this might help,
JS
Jan 25 '08 #4

P: 30
I tried it, but it doesn't work...

Please see the situation:

Here is the examples:
Table1: Table2:
Field1 Field2 Field 3 Field1 Field2 Field3
Cynthia 3/22/07 25 Cynthia 3/18/07 26
Cynthia 3/23/07 26 Cynthia 3/23/07 35
Cynthia 3/24/07 27 Cynthia 3/24/07 37

And this is the result when I create a SQL.

QueryTable
Field1 Field2 Field3 Field4
Cynthia 3/22/07 25
Cynthia 3/23/07 26
Cynthia 3/23/07 26 35
Cynthia 3/24/07 27 37
Cynthia 3/24/07 27
Cynthia 3/22/07 25

But the result I want is this:

Query Table:
Field1 Field2 Field3 Field4
Cynthia 3/22/07 25
Cynthia 3/23/07 26 35
Cynthia 3/24/07 27 37


If you notice In this query Table(the result I want) In Field 4 it has a 2(35 and 37) value and 1 no value.
just because In Table 1 the Date 3/22/07 has no corresponding value for Table2
(and I want to see it also, not only those record w/ corresponding value in table 2)

Table1 is my Primary Table and Table2 is my Secondary Table...
I want to get the value from Table2 that have the same date in Table1 without duplicating Date.

I hope you understand my explanation and I really need this output.
By the way thank you very much.....
:-)
Jan 26 '08 #5

MindBender77
100+
P: 234
I tried it, but it doesn't work...


Query Table:
Field1 Field2 Field3 Field4
Cynthia 3/22/07 25
Cynthia 3/23/07 26 35
Cynthia 3/24/07 27 37


:-)
I duplicated your 2 tables and this sql statement produced the result you mentioned above.
Expand|Select|Wrap|Line Numbers
  1. SELECT test_table1.Name, test_table1.Date, test_table1.[#done], test_table2.[#done]
  2. FROM test_table1 LEFT JOIN test_table2 ON test_table1.Date = test_table2.Date;
  3.  
JS
Jan 28 '08 #6

P: 30
Hi!
Thank You very much... it's works very nice.... thank you so much again...!!!!

:-)
Feb 4 '08 #7

Post your reply

Sign in to post your reply or Sign up for a free account.