extract desire count from a access table

[irfan.rangrej]

Hi,

I m using Access 2003.

I have 3 tables in an access like Table A, Table B & Table C , there is only one column in each table which contains unique records (It is possible that (Table A & Table B) (Table B & Table C) (Table A & Table C) have common records,).

Table A has 100 record, Table B has 50 and Table C has 30.

Common records between A and B are 40, between A and C are 20, between A,B and C are 10

Now I want 50 records from Table A which contains common record from B but not present in Table c.( Here common number between A and B are 40 out of that 10 record are there in Table C, so I can take only 30 records which are common in A and B, rest 20 are any records which are not present in Table C)

Currently I m able to do this by a long procedure, I want to do it by a single Query. Count of record are taken as sample actual data will be differ but in same forma.


sample access is attached…for your ease.

Re,
Irfan

[Kyle123]

I don't have access installed, but I suspect you want something like:

Select *
From TableA a
    inner join TableB b on a.Field1 = b.Field1
    left outer join TableC c on a.Field1 = c.Field1
Where 
    c.Field1 is Null  

[irfan.rangrej]

Thanks Kyle,

I am getting only 30 records from this query

SELECT TableA.Field1
FROM (TableA INNER JOIN TableB ON TableA.Field1 = TableB.Field1) LEFT JOIN TableC ON TableA.Field1 = TableC.Field1
WHERE (((TableC.Field1) Is Null));

were I need 50 records, rest 20 record are any which are not in tablec.


Re,
Irfan

[Kyle123]

This doesn't make any sense to me , if there are 50 records that are the same in A and B (but not in C) then that's what you'll get. As it stands there are 30 records that are common in A and B excluding any that are also in C.

[irfan.rangrej]

In table A I have my customer base, table B has segmented base to whom I want to promote my product, table C has customer base to whom product is already promoted.

Now I have promotion capacity of 50 customer. I don't want to repeat any customer and want to utilize my full capacity.

that is why I want 50 record, I know there are only 30 record common in A and B after excluding C, but Table A has 100 record and extra 20 record can be any out of remaining 70 but not present in C (already promoted).

As you said sometimes it is possible to get base by your query..but most of times I am facing this issue that common base after excluding promoted base is lesser then my promotion capacity.


Re,
Irfan

[Kyle123]

Hmm, how far does this get you?

Select top 50 d.*
From TableA a
    inner join TableB b on a.Field1 = b.Field1
    left outer join TableC c on a.Field1 = c.Field1
    right outer join TableA d on a.Field1 = d.Field1
Where 
    c.Field1 is Null  

[irfan.rangrej]

Hi Kyle,

I don't know much about sql. I did copy paste your code in access query sql view, but it is giving error "Syntax error(missing operator) in query expression 'a.field1=b.field1 left outer join TableC c on a.field1=c.field1 right outer join TableA d on a.field1=d.field1'. Please help in getting it work.


Re,
Irfan

[Kyle123]

I can't I'm afraid, I don't have access - the cod eis valid sql for sql server so I'm guessing it won't be far off for access. I suspect it'll need some brackets

[alansidman]

Try removing the word "outer" from your SQL statement.

[irfan.rangrej]

Hi,

As suggested by Alan I tried the code after removing "outer" from code given by Kyle but it’s not working & giving the same error.

I tried below query but that is also not working and giving error "The SQL statement could not be executed because it contains ambiguous outer joins. To force one of the join to be performed first, create the separate query that performs the first join and then include that query in your SQL statement."

SELECT TOP 50 TableA_1.Field1
FROM ((TableA INNER JOIN TableB ON TableA.Field1 = TableB.Field1) LEFT JOIN TableC ON TableA.Field1 = TableC.Field1) RIGHT JOIN TableA AS TableA_1 ON TableA.Field1 = TableA_1.Field1
WHERE (((TableC.Field1) Is Null));

[alansidman]

This worked for me and gave me the desired results you show:

Please Login or Register  to view this content.

[irfan.rangrej]

Thanks Alan,

This works for sample data but not for actual data.

In the sample data table A contains 1 to 100, table B 1 to 40 & 101 to 110.

Now as per the query it takes records from table A in ascending order, so all common records comes in top 50 records.

If you add 98 & 99 in table B then common numbers between A & B will be 32 after excluding table C.


In this situation this query includes only 30 common records as 98 & 99 comes after top 50 records.

Please help on this...



Re,
Irfan

[irfan.rangrej]

guys please help