0

I am trying to code an exe in MS Access that contains a loop of queries. The exe will take a csv file and loop through lines of code running multiple queries to execute different functions.

The loops are shown below in an image:

Query Loops

The queries already exist, can they be simply called into a loop similar to php variables?

Furthermore, can I set a variable that will carry through future queries as they are to be run in a particular looped order? (Such that the variable min_value is set in the first query and carried into the second query and third and so forth...

--------- Added at 1393846838 as requested by @ElectricLlama ---------

Queries are as follows:

set $time query

SELECT datediff("s", #1/1/1970#, now()) AS unix_time;

select minimum value query

SELECT Min(Field4) AS min_value FROM Market;

select * where min query

SELECT * FROM Market WHERE Field4=min_value;

count (min) value query

SELECT count(Field4) AS count_total FROM Market WHERE field4=min_value;

The necessity for a loop is due to some variables in one query being either null or 1 and in another query either being 1 or >1

Thank-you

--------- Added at 1393855784 as requested by @ElectricLlama ---------

Image

Improve this question
11
  • You can create a variable that will last the entire time Access is open (Global), or while a Form is open (form level, before first executable statement). I assume you are planning to run this process from a form? Yes, you can write one procedure (or function) that runs all of the logic you have drawn up. Commented Mar 3, 2014 at 0:00
  • That's corredct @WayneG.Dunn :) I will be running this from a form (exe is the wrong term). I am struggling to write said procedure or function to run this logic. Any guidance or direction that you can point me in please? Commented Mar 3, 2014 at 0:03
  • It's entirely likely that this can be achieved by combining a number of queries rather than performing any looping. If you want to proceed with the coding option at least post the code you have tried (i.e. Sub MyFirstTry End Sub If you would like to explore how this might be done with queries only, provide some sample data. Commented Mar 3, 2014 at 0:21
  • Hi @ElectricLlama, I haven't coded any macros but will edit my above post to include queries Commented Mar 3, 2014 at 0:36
  • To clarify, the looping thing will need to be done in VBA rather than an Access macro - are you familiar with VBA? It would also be helpful to post the structure of the CSV file. Commented Mar 3, 2014 at 1:24

1 Answer 1

Reset to default
1

I'm making some assumptions because your flowchart doesn't correlate with your sample data, but.... here is a sample query that automatically builds your line number. But it requires your data to be first loaded into a table with an autonumber in it. This would be a two step process but without any loops or complexities in it. If you can get this to work it will be faster and easier to maintain and debug than a big chunk of nested loop VBA code. Not to mention that your $time variable will not be unique if your loop takes less than a second to run.

SELECT 
T1.ID, T1.Field1, T1.Field2, T1.Field3, T1.Field4, 
(    select count(*) 
     from Table1 T2 
     WHERE T2.Field3=T1.Field3 
     AND T2.ID < T1.ID
) AS LineNum
FROM Table1 AS T1;

This query generates an incrementing line number against any records with the same Field3

Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

Hi @ElectricLlama, is there any way to loop through the queries without writing them directly into vb script?
If you run this query there is no looping at all. But addressing what I think is your question: Yes. You create MS Access queries, possibly with parameters. Then you use code inside VBA to refer the the 'querydefs', rather than the SELECT queries themselves. Again, your $time query will not solve your problem - it will produce duplicate line numbers if it takes less than a second to run.
I understand the time variable issue and duplicate line numbers don't seem to a problem thus far. It's more getting my head around the structure and design of vb code as opposed to php based coding (as I mainly use php in my work).
OK well I"ll just leave it at this - there is no VBA coding in my solution, nor is there any required. It's a set based solution. You might need a macro to tie together a few sequential SQL statements but there are no loops or coding.
Thats fine... all I'm getting at is that you have posed a 'procedural' solution to something that would probably be better solved in a 'set based' manner. If you are not comfortable pursuing the set based solution I can assist with the procedural solution also.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.