There can be only one! (1 Viewer)

[aliasquark]

There can be only one! **RESOLVED**

Need an SQL statement (or similar Microsoft Access trick) which queries a table and only produces the first occurence of a field called item and puts info of the next occurance(s) into a new field called info(x)

item___info
1_____1111
2_____2222
2_____222a
2_____n/a
3_____bla bla bla
3_____brother
3_____sister

will become:

item___info_____info2_____info3
1_____1111____ _____
2_____2222____222a_____
3___bla bla bla___brother___sister

I have been able to produce the fields info(x) and populate them appropriatly, however using item=3 in the example above I would actually get 3 records for when item=3 (one for each info(x) value)... so it would look like this

item___info_____info2_____info3
1_____1111____ _____
2_____2222____222a_____
2_____2222____222a_____
3___bla bla bla___brother___sister
3___bla bla bla___brother___sister
3___bla bla bla___brother___sister

In any case the statement I used for this was very very messy and I'm sure there should be a way to incorporate both the population of info(x) as well as the exclusion of any duplicated value whilst leaving the first occurance of that value in one SQL statement.

For the data I am using there will be a maximum of 5 possible info fields.

Hopefully the problem is clear enough, and the solution is easy enough.

Thanks in advance.
Q.

 

[Jon K]

Try this. Switch to query SQL View and add the word DISTINCT after the word SELECT so that the statement reads something like this:-

SELECT DISTINCT .....
FROM [Tablename]


If it doesn't work, try these two queries (type/paste each in the SQL View of a new query, using the correct table name in qryOne):

qryOne:-
SELECT [Item], [Info], "Info" & DCount("*", "TableName", "[Item]=" & a.[Item] & " and [Info]<='" & a.[Info] & "'") AS Inflx
FROM [TableName] AS a
WHERE [Info] is not null;

qryTwo:-
TRANSFORM Last(qryOne.[Info]) AS LastOfInfo
SELECT qryOne.[Item]
FROM qryOne
GROUP BY qryOne.[Item]
PIVOT qryOne.[Inflx];

Run the second query qryTwo.


Note.
qryOne assumes that [Item] is a numeric field. If it is a text field, it should be enclosed by single quotes in the DCount() function, i.e.

"Info" & DCount("*", "TableName", "[Item]='" & a.[Item] & "' and [Info]<='" & a.[Info] & "'") AS Inflx

 

[aliasquark]

IT WORKS!!!

Thanks so much, this is the best thing that has happened to me all year so far!

(Sad I know)


Just one problem whenever the info field has an apostrophy the field called influx returns a value #Error.

Example: info = we're

I think that you might have suggested a solution in the situation where [item] is a text field (which it is) however I am a unable to get it to work in the above situation (and possibly if there are alphabetic - and especially apostrophies in the field [item] )

when there is an alphabetic character in [item] the whole thing breaks down, and when there is an apostrophy in [info] i get #error

 

[Jon K]

The apostrophe is one of the characters reserved by Access for data typing.


A workaround is to use a subquery (i.e. a Select statement in brackets) that doesn't require data typing, instead of the DCount() function which needs data typing, in the first query to create a new table, and then base the second query on this new table.

newQryOne:-
SELECT [Item], [Info], "Info" & (Select Count("*") from [TableName] where [Item]=a.[Item] and [Info]<= a.[Info]) AS Inflx INTO [NewTable]
FROM [TableName] AS a
WHERE [Info] is not null;

newQryTwo:-
TRANSFORM Last([Info]) AS LastOfInfo
SELECT [Item]
FROM [NewTable]
GROUP BY [Item]
PIVOT [Inflx];


Run the first query to create the NewTable, and then run the second query. It should work whether there are apostrophes or not in [Item] or [Info].

 

[aliasquark]

Thanks, all's well.