Solved

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

Posted on 2004-04-20
8
1,017 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

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…
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

636 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