Solved

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

Posted on 2004-04-20
8
1,012 Views
Last Modified: 2012-06-27
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

0
Comment
Question by:pwill40
  • 4
  • 3
8 Comments
 
LVL 65

Expert Comment

by:rockiroads
ID: 10867732
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

0
 
LVL 1

Expert Comment

by:dssdb
ID: 10868043
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]


0
 

Author Comment

by:pwill40
ID: 10877330
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?
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 10877396
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

0
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

Author Comment

by:pwill40
ID: 10887616
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

0
 
LVL 65

Accepted Solution

by:
rockiroads earned 200 total points
ID: 10887689
on the form, you could put two textboxes which hold the start date/end date e.g. txtFDate, txtTDate

no need for macros

the command button has a on click event where you put your vba code

you can access the textboxes on the form directly like this  txtFDate.Value, txtTDate.Value

to loop, you can do this

'Assuming valid dates entered

'Get Date Difference
iDaysDiff = DateDiff("d",txtTDate,txtFDate)

for i = 0 to iDaysDiff
    .. do your stuff

    'Insert
    DoCmd.RunSQL "INSERT SQL Statement"
next i
0
 

Author Comment

by:pwill40
ID: 10887807
Thanks that's a great help.

I had no idea how to approach this, but now definately have a plan...
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 10887848
ok, good luck
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Run Time Error 3075 15 45
Running sum query 6 33
selection of current record jumps to a non-selected record 8 37
Continuous form : Prefilled Data 7 18
Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

895 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now