If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > PC based Database Applications > Microsoft Access > Insert a loop into a query

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
Join Date: Jun 2009
Posts: 3
Insert a loop into a query

Hello everyone !!

I am a new user of microsoft access because it is the database used at my work. This database enables you to find information on flight schedules at an airport through forms that command queries. One existing form enables to retrieve all the flights for a specific date. You can also refine your search to a specific airline, a concourse or a gate.
My objective is to obtain all the flights for a Period defined by a start date and an end date. I need exactly the same information given by the existing query but for a period. I need to introduce a loop that apply the SELECT query to every single day of the period because some flights occur only a few days per week. And I canno't introduce a loop when designing a query on Access.

I ve spent hours on google to find some assitance but i didn't find helpful information. I think I need to use VBA to create the complex query but I absolutely don't know VBA. The idea would be to copy and paste the SQL code of the existing query and then modify it into VBA. But this is just an assumption.

I have already worked on mysql so I have a small background on database.

I found helpful information on this forum in the past and I thank you for your assistance.
Reply With Quote
  #2 (permalink)  
Old
L33t Helpa Munky
 
Join Date: Nov 2007
Location: Adelaide, South Australia
Posts: 4,049
Ok, so I'm confused on this one. No, you definitely cannot put a loop in a query. So, now that's established, I have to admit I have zero idea what you want to do.

Without using database terminology, what is it you want your query to show you?
__________________
Owner and Manager of
CypherBYTE, Microsoft Access Development Specialists.
Microsoft Access MCP.
And all around nice guy!


"Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
"...teach a man to code and he'll be frustrated for life! " -- georgev
Reply With Quote
  #3 (permalink)  
Old
Moderator
 
Join Date: Dec 2004
Location: Madison, WI
Posts: 3,925
Here's something you can try but I'm not sure if it'll achieve what you want.

1. Create a function in a module... (Note: you can use functions which return values as an expression in a query column to return data.)
ie.
Function GetInfoFunction(RID as variant) as variant <- Note: RID is the recordID (autonumber) or unique identifier in the table being passed to this function which will then return the value you populate GetInfoFunction with.
dim rs as adodb.recordset
set rs = new adodb.recordset
dim strSQL as string
strSQL = "select * from MyTable where RecordIDField = " & RID & ""
rs.open strSQL,currentproject.connection,adopendynamic,adl ockoptimistic
.....
..... (do your calculations and other totals or just return certain data here..)
GetInfoFunction = rs!SomeField (or GetInfoFunction = myCalculatedValueVariable) <- ie. the function then returns whatever value is in GetInfoFunction.
rs.close
set rs = nothing
end function

Then in your query in a blank column, put in an expression -ie..

CallLoop: =GetInfoFunction([RecordIDField])

(or try this for simplicity to see how it works - save this function in a module)
Function GetHello() as string
GetHello = "hello"
end function

and in the query expression: MyHello: =GetHello()
You'll notice in your query for that expression column, the wording "hello" will be returned for all the records in that column.

I put functions in queries which work very well but they can take a toll calling the function for every record (especially on large datasets!)

Usually I'll utilize a function in a query to return a value (versus using the dlookup in a query as the function needs to do some kind of calculations upon opening the recordset). ie...GetTotalDollars: MyReturnTotDollarsFunction([RecordID]) (for example to return the total dollars for the specific RecordID value. The MyReturnTotDollarsFunction would then return the total dollars from whatever recordset the function opens and sums for that specific RecordID passed to it.) I don't use this method often as it does again, take a toll on the query (ie. calling the function for every record returned) but it's good for smaller recordsets.

I'll especially use it with the getuser() function (ie. in the codebank) to return the current user in my append queries. (Note also: you can pass multiple "field" values to a function (to use in your SQL statement/calculations) but they can return only 1 value.)

ex: MyExample: =GetNumDays([StartDate],[EndDate]) where the GetNumDays function performs some calculations (which can't be done using the dateadd() function in the query) on the 2 dates passed to the function and displays whatever value GetNumDays returns. (Note: StartDate and EndDate are actual fields in the table just like RecordID in the example above.)

Hope that helps.
__________________
Expert Database Programming
MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Last edited by pkstormy; 06-25-09 at 00:03.
Reply With Quote
  #4 (permalink)  
Old
Registered User
 
Join Date: Sep 2006
Location: Surrey, UK
Posts: 959
What sort of access do you have to the database (user, designer, administrator, etc)? If you have some form of design access, you can change the enquiry form to have two date fields for defining your search period. Then you amend the query that sits under the form to that it returns all records that fall between your specified dates.
__________________
10% of magic is knowing something that no-one else does. The rest is misdirection.
Reply With Quote
  #5 (permalink)  
Old
Registered User
 
Join Date: Jun 2009
Posts: 3
Insert a loop into a query

Quote:
Originally Posted by weejas
What sort of access do you have to the database (user, designer, administrator, etc)? If you have some form of design access, you can change the enquiry form to have two date fields for defining your search period. Then you amend the query that sits under the form to that it returns all records that fall between your specified dates.
Thank you for your answers

I have a design access, and the original query was designed through the design interface (not by me). Weejas, could you give me some details to implement your solution ?
Reply With Quote
  #6 (permalink)  
Old
Registered User
 
Join Date: Sep 2006
Location: Surrey, UK
Posts: 959
If you post the SQL from the underlying query, I or someone else will be able to advise you on which bits to change. Don't forget to add the extra field to the enquiry form as well!
__________________
10% of magic is knowing something that no-one else does. The rest is misdirection.
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On