Combining Queries

[Deleted member 143609]

Total newbie here so hello to all. Have started using Access/SQL in work so hoping learn a lot from the Access community! I’m reasonably comfortable with Powershell and C++ but that’s as far as my experience goes unfortunately so I’m going to dive right in the deep end here with a big question:

I’ve created two small queries (sad how long it actually took me..) and while they run well and output a value, I’m hoping I can combine the two to run as one. The reason being is because I’m doing all of this through Powershell (very convoluted but the set up in work is bizarre) and rather than running heaps of different queries, I could reduce the number just by combining the queries that are used, particularly the ones that involve the same tables. Here’s an example:

SELECT TOP 1 Max(Round(Val([NodeNameIdMap]![StringVal]),0))+1 AS [Max Node]
FROM NodeNameIdMap
WHERE len([NodeNameIdMap]![StringVal]) = 14
AND
LEFT(NodeNameIdMap.[StringVal], 6) = '900101'
AND
(NodeNameIdMap.StringVal) NOT LIKE '*[-]*'

This just selects the highest node number (Not all the values in the column are the same format so the condition is that ‘Max Node’ 14 digits long, starts with 900101 and doesn’t have a ‘-‘ sign.).

UPDATE NodeNameIdMap
SET NodeNameIdMap.[StringVal] = [Max Node] +1
WHERE LEFT(NodeNameIdMap.[StringVal], 6) <> '900101'
OR ((NodeNameIdMap.StringVal) Like '*[-]*') 
OR ((Len([StringVal]))<'14') 
OR ((Len([StringVal]))>'14');

This query updates every value that has the parameters mentioned above with the [Max Node] from the first query. Obviously I want it to increment by 1 for each node, starting with the max mode number. I have two issues though: The second query replaces every value with just the max node number and doesn’t increment by 1 for each one and secondly, I can’t seem to combine the two queries. I’ve tried UNION, UNION ALL and INNER JOIN but unfortunately nothing seems to be working.

Is this type of combination just not possible in SQL? I apologise for the long post (especially since it’s my first!) but I’m just trying to kill two queries with one stone. Thanks!

 

[CJ_London]

the max node value will be 'determined' when the query starts to run, it won't change until a new record has been appended - but all the appends occur after the query has started to run.

What you will need is a 'running count' to add to the max+1 value. To get the running count, you need a unique value in your table (might be an ID, might be a timestamp, or something else).

Something like

SET NodeNameIdMap.[StringVal] = [Max Node] +1+
dcount("*","NodeNameIdMap","LEFT(NodeNameIdMap.[StringVal], 6) <> '900101'
OR ((NodeNameIdMap.StringVal) Like '*[-]*') 
OR ((Len([StringVal]))<'14') 
OR ((Len([StringVal]))>'14')) AND ID=<" & ID)

May have got some brackets wrong but you should get the idea.

It would be faster to use a subquery, but you can't use subqueries with update queries

 

[Deleted member 143609]

Hi CJ,

Thanks very much for the help, I'll get around to trying a running count today.

Just wondering though, have you any idea why the UNION command would work in connecting the two queries? I've looked up different errors saying that the numbers of columns don't match and such but I'm still confused as to UNION wouldn't just carry over the Max Node value from the first query.

Again, thanks for the help (and the quick response!)

 

[CJ_London]

not where a union query comes into it - union queries join data vertically, not horizontally so your max would be in a different record

 

[Deleted member 143609]

Ok no worries, I'll get working on it anyway. Thanks for your help!