Obtain unique data sets (rows)

[cococrunch89]

hi,

Can anyone help with this? Something seems to be wrong except I cant figure out what it is.

I am trying to sort my data such that if [SP].time has appeared when [SP].series = "SPJ14-SPK14" then the data for the other 2 series will not show up. i.e. SPJ14 and SPK14. In other words, the time is unique.

SELECT DISTINCT Last([SP].Series) AS LastOfSeries, [SP].Time, [SP].BidOrAsk, [SP].Price, Max(Len([Series])) AS Length
FROM [SP]
GROUP BY [SP].Time, [SP].BidOrAsk, [SP].Price
HAVING (((Last([SP].Series))="SPJ14" Or (Last([SP].Series))="SPK14" Or (Last([SP].Series))="SPJ14-SPK14"))
ORDER BY [SP].Time, Max(Len([Series]));
 

Thanks in advance!

[zmbd]

I think that your issue is here:
(Last([SP].Series))="SPJ14-SPK14"))

It looks like you're asking for a span based on a text value and, to the best of my knowledge that isn't directly possible.

However, without an example of the data it's difficult to tell, can you provide about 4 or 5 lines of the data?

Use the [CODE/] button to insert the following: [code] [/code]

Between these two tags, you can put some fomatted data:
[code][SP].Series, [SP].Time, [SP].BidOrAsk, [SP].Price, [SP].[Series]
[seriesdata1][timedata1][bidoraskdata1] ...
[seriesdata2][timedata2][bidoraskdata2] ...
...[/code]

You might also include an example of what you want to have happen too.

You do NOT need to attach any files at this point.

Next, you really should not use the word "Time" as a field name. It can cause issues as "Time" is a reserved token:

• Access 2007 reserved words and symbols
• AllenBrowne- Problem names and reserved words in Access

[cococrunch89]

Here's how my data looks like now

[SP].Series, [SP].Time, [SP].BidorAsk, [SP].Price, len[series]
[SPJ14][11:23:20][Bid][200][5]
[SPJ14][11:23:20][Ask][205][5]
[SPK14][11:23:20][Bid][200][5]
[SPK14][11:23:20][Ask][205][5]
[SPJ14-SPK14][11:23:20][Bid][10][11]
 
 

so when the time is the same such as in the sample data above, I want to hide/delete the series-SPJ14 and SPK14 and just show SPJ14-SPK14


Thanks a lot!

Sorry if I'm not typing or I don't seem to understand much database lingo. This is my first time using access

[Rabbit]

What you can do is outer join the table to itself on the first half and second half to see if there is one that encompasses multiple records.