Problem using SELECT FIRST in TOTALs query 1

[VickyC]

hello to all

I am asking for help with a problem I'm having with using SELECT FIRST in a TOTALs query.

Here's the situation:

a) In VBA, I generate code to INSERT INTO the table shown below in b). So far, so good.


TABLE1
======

b)   AutoID	ID_X	ID_Y	Val1	Val2  (nb: Val2 = 1 + ABS(Val1))
     =======================================
	1001	56	288	2	3
	1002	56	288	8	9
	1003	56	288	32	33
	1004	56	288	-4	5
	1005	56      288	-8	9
	1006	56	288	-2	3
	1007	56	288	 4	5
	1008	56	288	16	17
	1009	56	288	-32	33	
	1010	56	288	-16	17	

	1011	56	289	2	3
	1012	56	289	8	9
	1013	56	289	-32	33
	1014	56	289	-4	5
	1015	56	289	-8	9
	1016	56	289	-2	3
	1017	56	289	 4	5
	1018	56	289	32	33
	1019	56	289	-16	17
	1020	56	289	16	17

etc...

Note that for each (ID_X,ID_Y) combination Val2 will always the same 7 pairs of numbers
differing only in their order.


c) Now I want to GROUP BY ID_X, ID_Y, Val2 and determine the value of a new field, Z as shown
in the SQL below.

    SELECT T.ID_X, T.ID_Y, T.BVal2, IIf(FIRST(T.Val1) > 0, 1, 0) AS Z

    FROM TABLE1 T		

    GROUP BY T.ID_X, T.ID_Y, T.BVal2	

    ORDER BY T.ID_X, T.ID_Y, T.BVal2;

d) The desired output is...

TABLE2
======

ID_X	ID_Y	Val2	Z
===================================================
56	288	3	1         because the 2 comes before the -2 in TABLE1
56	288	5	0         because the -4 comes before the 4 in TABLE1
56	288	9	1         etc...
56	288	17	1
56	288	33	1

56	289	3	1
56	289	5	0
56	289	9	1
56	289	17	0
56	289	33	0

etc...

e) FINALLY - here is the problem!!!

*** I use this code on very large tables, and it works 99.999% of the time. But on rare occasion I get an error in the Z column (1 instead of 0, and v.v.)
Also, the error seems sporadic - I can rerun the same code only to see an error in a record that was previously correct!

I'm sure that the problem is that FIRST really only has meaning in an ORDERed list. But, this is a TOTALs query, so I don't really have any columns I can use to 'nail down' the order.

Any help will be greatly appreciated. It's been hard to even narrow down the error source this far, because the errors occur so infrequently. Vicky

 

[Andrzejek]

How do you get the data to come from TABLE1? Do you have any ORDER BY?
If you [tt]ORDER BY ID_X, ID_Y, Val2 (BVal2 ?)[/tt], ID_X has 20 records of 56, ID_Y has 10 records of 288, Val2 - you have multiple of 3, 5, 9, etc. Your ORDER BY may give you different order of records every time you ask for it.

I think if you want to use FIRST, you need to ORDER your data by something that will make it ordered. Remember: just because you ask for the data without any 'working' ORDER BY part and you SEE your data ordered, that does not mean your data will always be ordered the way you want it to be. Data in any table is not kept in any order, you need to order it when you ask for it.

Just my opinion.

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[lameid]

Assuming 2 things, AutoID is an autonumber column and is the physical sort you are desiring...

Query1:

SELECT T.ID_X, T.ID_Y, T.BVal2, Min(T.AutoID) AS AutoID

    FROM TABLE1 T		

    GROUP BY T.ID_X, T.ID_Y, T.BVal2	

    ORDER BY T.ID_X, T.ID_Y, T.BVal2;

Query2:

SELECT T.ID_X, T.ID_Y, T.BVal2, IIf(T.Val1 > 0, 1, 0) AS Z

    FROM TABLE1 T Inner Join Query1 Q1 on T.AutoID=Q1.AutoID

    GROUP BY T.ID_X, T.ID_Y, T.BVal2	

    ORDER BY T.ID_X, T.ID_Y, T.BVal2;

You may have better luck joining on more columns... or not.

 

[VickyC]

thanks Andrzejek and lameid for responding. I was able to modify lameid's code a bit to get to a solution. Again, thanks. Vicky

 

[Darrylles]

Nice to hear it Vicky, but how about sharing that solution in order that other people may benefit (as this site intends) - and not just you?

Darrylle :-(

Never argue with an idiot, he'll bring you down to his level - then beat you with experience.

 

[VickyC]

hi Darrylles - I had stated the question in a simplified way that still captured the problem I was having. So, the modifications I mentioned were to apply the given answer to my actual situation. In fact, Andrejek and lameid's responses answered the question, as stated, completely.