Solved

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

Posted on 2004-04-20
8
1,015 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

749 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