Solved

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

Posted on 2004-04-20
8
1,013 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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
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
 

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

Back Up Your Microsoft Windows Server®

Back up 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.

Question has a verified solution.

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

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

810 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