Interesting Duplicates Issue

[mclee021]

Hey all,
This is probably a real simple problem, but one that I can't quite wrap my head around. I have some environmental data that I need to display a sample date for. I have four different tables (Fish, Habitat, N03, and Inverts) that each contain data sampled on a certain date. They were all sampled at specific sites, which is my unique identifier. I was able to get it in the format I wanted, but it contained duplicates. It seems that the query returned all possible combinations of dates when I just wanted the unique dates. For example, on the attached screen shot the site contains 16 records, when there are really only 8 unique combinations of dates. It gave me all possible combinations of dates from all the tables, when I really just needed the unique combination of dates.

I'm so lost any help would be great. I can certainly post more info if required. Thanks all.

 

[SimonB1978]

Hi,

Could you post you query (in SQL) and a sample of what you expect as a result?

 

[Brianwarnock]

There are infact 16 combinations of dates 2 from each of the 4 types.

I take it that you are not going to supply the info requested by Simon, so no point asking for table layouts aswell.

I took a geuss but only used3 tables and i think the best you can hope for is as per attached.

Brian

PS sorry its in a Doc that was automatic and so I left it.

 

[mclee021]

Hey all. Sorry for the late reply. I work for the US Gov, and our technology is so rediciously outdated that my reply kept freezing up. Thanks for all your responses Simon and Brian. Ok. So I don't have the SQL from the query because I brought it out of Access and into SPLUS to get the table format I wanted. I originally started with four different Access tables containing different types of data (fish,etc..). The only thing that is unique among the data is the site they were sampled at. I queried each of the four tables separately to just the site (Name) and the sample date (VisitDate). I then took them into SPLUS and combined the data into the format in the pic I posted. This is exactly what I need. I need to be able to see if all types of data were sampled in the same year. The end product can contain site duplicates, but the visit dates for each of the four types of data can't repeat. Brian I think what you posted is what I'm trying to get. SPLUS gave me all possible combinations of dates from all four tables. For example, a site will appear 16 times when I really know there are only 8 unique shared dates among them. If I could just get something to only show me the unique combinations of dates. Some sites were not sampled at all, and null values are fine for those. Thanks again. Hope this helps. I will try to attach some of the data tomorrow becauese it will probably help more than my explaining the issue. Thanks again.