Link to home
Start Free TrialLog in
Avatar of pwill40
pwill40

asked on

Building an Access Application... where do I start?

Hi,

I'v not really used Access much and could use some guidance on how to best go about building an application.  This is what I'm trying to acheive..........

Currently I have a access database with serveral tables in it.  These tables contain a lot of historical data.  I have built a frontend for this table in Excel (which I do know how to use) that takes data from the access database runs several fairly complex macro's to work out cumulative data totals and tracks the performance of the data.   Becuase of the type of analysis I am doing on the data (long time periods), and due to the fact that is has gradually got more & more complex these macro's are now taking several hours to process & output the results.

I am assuming that by moving the model from Excel into an Access application I will greatly improve the performance?  

Is it possible to run queries with a range of values?  i.e.  If I wanted to run a query using today's date, then again using yesterday's date etc...    If so what is the best way of setting this up?

Does anyone have any links to good examples/tutorials of how to build access applications?

Any help would be greatly appreciated.

Many Thanks,
Paul

Avatar of rockiroads
rockiroads
Flag of United States of America image

Moving to Access will help speed things up because you can define indexes on certain fields - this speeds up searches

you can search by almost anything, and yes you can seach by date, yesterdays date etc

builtin functions like Now() return the current date, functions like DateDiff, DateAdd allow you to edit date values

check this out from microsoft

http://www.microsoft.com/accessdev/articles/BuildApp/Bapptoc.htm

Avatar of dssdb
dssdb

A simple solution for using a varying date in an Access query is to put this in the criteria of your date field:

[Enter the Date]

When you do that it'll pop-up a prompt saying "Enter Your Date" each time you run the query.

For a date range you can use
>=[Enter Start Date] and <=[Enter End Date]


Avatar of pwill40

ASKER

Say I want to enter a date range into a form i.e. start-date = 20040101  end-date = 20040115 and run the query over and over for each of the day's in the range, do I  need to write scripts/macro's to call the query 15 times or is there any way to do it automatically?  

Also is it easy to pass variable from form's into queries?
to use a form value in a query (note form must be open at the time)

in your query you can do this

e.g. SELECT <fields> FROM <table> WHERE <myField> = [Forms]![<name of form]![<name of control on form>]

what kinda query do you wish to run when specified a date range, pass a date to one query?

you can create loops to run these queries which will do it the number of times as expressed in your date range

things like DateDiff give the number of days between two dates

Avatar of pwill40

ASKER

From what your saying I think I'll need to create a loop to run the query, due to the way I need the data output.  I'm assuming I could do something like..

- create a form with start and end date fields
- have a button which started a macro (or something)
- macro gets dates from forms
- macro loops from start date to end date executing the query for each date.
- macro writes reults to a further table

nb. this is just one small part of what I need to do but it's the bit I'm having most difficulty visualising how it will work

ASKER CERTIFIED SOLUTION
Avatar of rockiroads
rockiroads
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of pwill40

ASKER

Thanks that's a great help.

I had no idea how to approach this, but now definately have a plan...
ok, good luck