Select distinct, then select the FIRST occurring ID

[Eowyne]

Good evening good people on these forums!



I'm sure there are more people asking this question, I however don't have a clue as to how to search for it..
so here goes:

I have a Table, which I'll call 'Table' for now.

This table contains the following fields (note: this is an exerpt, I only need these 4 fields)

Table.ID, Table.BRKNo, Table.PalletNo, Table.Tab, Table.SN, Table.Asset

and these 4 fields contain the following 4 values:

[ID][BRKNo][PalletNo][Tab][SN][Asset]
[11][BRK01][Pallet01][Tested][123][As322]
[12][BRK02][Pallet01][Tested][623][As1242]
[13][BRK02][Pallet01][UnTested][234233][As1212]
...
[27][BRK02][Pallet02][Waste][23425][As212]
...
[39][BRK03][Pallet01][Tested][123][As112]

and so on and so on.

Since this old database has been a project from an ex colleague of mine, I'm doing a bit of normalization, and decided to cut off the Tab field from this table, and stick it into another table:

TableTabs
which will contain the following fields:
TableTabs.ID, TableTabs.BRKNo, TableTabs.PalletNo, TableTabs.Tab

however, since the old database was already in use and got kinda screwed up due to some bad coding (see ID 12 and 13 in the prev. example)

This is what happened:

A client would insert a pallet into a BRK (which is just code for a project) and assigns a tab to that (Tested, Not Tested, Waste). However, every pallet can only have one sort of Tab. Meaning that The record with ID 12 is correct, and the ID #13 is a bad one (got kinda screwed up with Tab.Defaultvalue on the forms).

So, here's what I'm trying to do:

I'm creating a select query that selects the BRKNo and PalletNo and Tab values as distinct. This is not the problem, this gives me:

[BRK01][Pallet01][Tested]
[BRK02][Pallet01][Tested]
[BRK02][Pallet01][UnTested]
and so on.

However, for my new table population, I need only the FIRST record on that BRK - Pallet combination (so that I get the following result in my table

[BRK01][Pallet01][Tested]
[BRK02][Pallet01][Tested]
[BRK02][Pallet02][Waste]

and so on

for this, I need the ID. however.. selecting the ID with the rest of the query just gives me all the other results as well.

is there a way for me to select the combination of BRKNo, PalletNo, Tab and the FIRST value of ID occuring in this combination?


----

By the way: Yes, I am sure that the FIRST ID occuring in this combination is the correct Tab to insert into the new table. Since this is created when the user creates the actual pallet in the database.

Untill now i've only found out the following code:

SELECT BRKNo, PalletNo, Tab FROM Table GROUP BY BRKNo, PalletNo, tabblad

this gives me exactly what I want.. except for the duplicate values on (in this example) BRK02, Pallet01.

Thanks in advance,

And don't mind to ask me for more explaination.. I know what I want, yet it's a bit hard to explain to people that do not

ps. I only need to run this query once.. but it has to be done since there are like 50.000+ unique BRK's and Pallet combinations.. and I do not like to do that all by hand ^^

[Eowyne]

Right now I'm thinking of using the above query and then use VBA in combination with VarPrev and VarNow variables to determine if the pallet that i'm adding is in fact already added.. I think this is a rather 'nasty' solution, but workable.

What do you people think?

[kennejd]

Try first(BRKNo) instead of grouping by it. Do some double-checking to see if that is accurate...there are always other options if that doesn't work...

[Eowyne]

This will make my query look like this right?

SELECT First(BRKNo),PalletNo,Tab FROM Table

I don't think this is it.. cause this gives me an access error without an error code.
And because my access is Dutch and You are English, I'll try my best to translate it to something understandable: (In the hope that I can do that, I prefer error messages in English too... )

---

I hope this is the correct one:

You tried to execute a query that does not include the specified expression 'PalletNr' as part of an aggregate function

p.s. I run this one in the SQL designer, and I receive the non numbered error while executing it to the dataview

ah nice.. running this code from a form gives me error code 3122, which is in fact the error listed above ^^

[kennejd]

I should have explained it a little better. In the Total row that comes up as 'Group By' as the default in your totals query, there are a list of options. There should be one called 'First'. The rest of the columns will be 'Group By'.

If you wanted to paste directly into sql, it would look like this:
SELECT First(BRKNo),PalletNo,Tab FROM Table GROUP BY palletno, tab

[Eowyne]

hmmm, these give me very strange results:

it skips several Pallets per Broker:

(e.g.:
BRK02 | Pallet02 | TESTED
BRK02 | Pallet09 | TESTED
BRK03 | Pallet01 | WASTE
)

Hmm I though that this occured because of the fact that i'm also using the Tab as a group By field, but that seems not to be the case, since Pallet03-010 are all TESTED pallets, they just don't seem to display..

----------------


editing the code to:

SELECT First(ID),BRKNo, PalletNo, Tabblad FROM tbl_refurb_invoer GROUP BY BRKNo, PalletNo, Tabblad ORDER BY BRKNo,PalletNo, Tabblad

appears at first to do the trick.. however, it still selects the wrong values from a pallet too. (as in my first example)

[Eowyne]

What my ideal but apparently not available solution Query would be is something like this:

Select ID, BRKNo, DISTINCT(PalletNo per BRKNo), Tab FROM Table ORDER BY BRKNo, PalletNo, ID...

this is however a complete non-sense SQL.. but I'm wondering if there is something like it that will actually do this trick?

[kennejd]

Would it be the first palletno in your groupby? Try a couple of different variations.

[Eowyne]

hmm, I am trying that however:

SELECT First(PalletNo),BRKNo,Tab FROM table GROUP BY BRKNo, Tab ORDER BY BRKNo, Tab

suddenly gives me an inputbox with 'Query1.PalletNo', while

SELECT First(ID),BRKNo, PalletNo, Tab FROM table GROUP BY BRKNo, PalletNo, Tab ORDER BY BRKNo, Tab

works just fine..

there's obviously something that i'm doing wrong here, but I totally fail to see it at the moment

(p.s. I never used the Group By function, always prefered the DISTINCT function, but that's not going to help me now I'm afraid)


-----------


By the way:

SELECT PalletNo,BRKNo,Tab FROM table GROUP BY PalletNo, BRKNo, Tab ORDER BY BRKNo, PalletNo, Tab

This code gives me the exact same as everything with 'first(ID) or first(BRKNo)' in it


---

anyway;

I want to thank you for looking in to my problem.
I hope that after a good night sleep this whole problem would be something like: 'Oh Snap! I so totally forgot about that or that', like i've had had so many times in PHP, forgetting comma's and stuff.
Right now i'm too tired to even write this message.. (edited it 4 times by now ^^)
So I'll check back here tomorrow.

Good night!

And once again: Thanks for the help so far!