Loop???

[TheBlondOne]

Hi all, im struggling with a report im trying to create, i've got an access database which contains 2 postcode lists in 2 tables, the 2 lists have no primary key to link together as they have both been imported into access via a .txt file. What i need to do is to supress any records where the postcode matches, this is where the problem starts, because there is no link between the 2 postcode lists they are displayed in Crystal in the order they are in the database, what i would like to do is to scan through all of the records in the report and then display whether or not there are any matches throughout the report and if there are matches i would like there to be an indication so that i can then supress the record and only display the postcodes where there are not any matches. Can anyone please advise me as to how i can achiecve this. If i haven't explained this properley please let me know and i will try to explain some more. Hope all that makes sense.

Thanks in advance

Steve

 

[vidru]

You need to do use a full outer join on the two tables. I don't have Crystal in front of me, so can't remember the syntax, but if you've got Crystal 9 or higher, you could use a SQL Command like the following to return only the non-matches between the two tables:

SELECT Table1.PostCode, Table2.PostCode
FROM Table1
FULL OUTER JOIN Table2 ON (Table1.PostCode = Table2.PostCode)
WHERE Table1.PostCode IS NULL OR Table2.PostCode IS NULL

Not sure if that syntax will fly in Access though, but you get the drift

-dave

 

[vidru]

Upon further review, I'd do it this way...

Create a UNION query in Access like this:

SELECT Table1.PostCode
FROM Table1
LEFT JOIN Table2 ON (Table1.PostCode = Table2.PostCode)
WHERE Table2.PostCode IS NULL
UNION ALL
SELECT Table2.PostCode
FROM Table2
LEFT JOIN Table1 ON (Table2.PostCode = Table1.PostCode)
WHERE Table1.PostCode IS NULL;

Then use that as your data source in Crystal. Should only return the non-matching Post codes in your two tables.

-dave

 

[TheBlondOne]

Thanks for your help Dave i'll try your ideas tommorow because unfortunately im involved in meetings for the rest of the day, thanks for your time.

Steve