consultant

[Justine]

I am a fairly new access user. In the last 3 months I have built 1
database and have started on a second one. The first one is working
adequately, but still not doing exactly what I want and I am having
trouble getting the second one going as it is going to require alot of
fairly complex calculations. I have absolutely no experience writing
code. I have looked into local access courses and I don't think they
are what I want. I believe what I need is an ACCESS consultant to
spend a few hours with me (or more) one on one. Has anyone gone this
route? Can anyone suggest how I might go about finding someone like
this? I have googled and not been happy with the results. I am in
Vancouver Canada. Any suggestions, tips or other would be greatly
appreciated.

 

[Jeff Boyce]

Justine

Have you checked with a local college or university? While I am sure you
could find folks willing to provide a couple hours consultation for a fee,
you may be able to find a student willing to help for the
experience/practice.

Also, your situation may be just what these newsgroups are here for. If
you'll post a more complete description of what you are attempting to do,
one of the newsgroup readers may be able to offer a lead...

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/

 

[Justine]

Hi Jeff,

I hadn't thought of a college/university - I may end up looking into
that..thanks.

What I'm trying to do is I guess kinda conceptual at the moment. I
need a database to track all of our leased properties. Currently this
information is in an excel document that is a bit of a beast and not at
all user friendly. I would like to be able to run custom reports.

The database needs to:

1. Contain all pertinent lease information such as location (physical
and legal description), landlord contact information, base and
operating costs per square foot, parking costs, lease terms, option to
renew, etc.
2. Calculate total current annual costs (base costs, operating costs,
parking costs, pst, gst) per lease and total of all leases, as well as
provide annual projections for next fiscal year.
3. Some of the issues I am having are that for some of the leases (5 to
10 yr terms), base cost and operating costs per square foot of rentable
area change during the term. Also, the reports and information I need
to provide are for our fiscal year, which is usually not the same as
the lease year (ie the lease rate, depending on each individual lease,
could change at some point during the fiscal year). Also some leases
will begin or finish during the fiscal year.

What I have done so far is to create the following tables:
1. Premise information (containing all the particulars in 1 above)
2. Lease type (for combo box in 1 above)
3. Year (ie 2005/06 to 2013/14) - not 100% sure where I was going
here.

I have also begun to build the form and start work on calculations.
Unfortunately I am now stumped, particulary with how to deal with lease
rate changes during the fiscal year. I was trying to put calculated
controls on the form but as each lease is unique not only in the terms
(ie when it starts and ends), but in whether the rate changes during
the year. I would be able to handle the calculations if all the leases
were the same as our fiscal year and the costs per sq ft didn't change
throughout the year!!

Any ideas, direction, encouragement appreciated....maybe Access can't
do what I want..

Thanks

Justine

 

[Jeff Boyce]

Justine

From your description, you have a pretty well-documented set of
requirements. Consider posting "how do I" questions to the various access
newsgroups here. You'll be more likely to get a response if you keep your
question narrow, and on topic for the newsgroup.

The first step in building a relational database is to identify the things
(persons, places, things, concepts) about which you want to keep
information, and how they are interrelated. This is also known as
entity-relationship modeling. The process of building a well-normalized
table in a relational database is not a common activity, and you can paint
yourself into some serious corners if you take data from a spreadsheet and
simply copy it into Access.

I didn't see anything in your description that caused me concern about
whether Access could handle it. Be aware, though, that Access is not a
spreadsheet, and has a bit of a learning curve.

Good luck!

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/

 

[Justine]

Okay - I can see what I am asking is quite broad! I will try to
narrow down my first question. If someone wants to suggest another
group where this would be more appropriate please do so.

I would like to create a form (template) where I can enter lease term
information, for example - lease start date - October 1, 2007 and
lease end date September 30th 2012. I would then like to calculate a
monthly base lease cost and a monthly operating lease cost based on
rentable area x applicable sq ft rates for base and operating. If all
leases had the same terms I could do it in a minute with a query or
calculated controls by simply multiplying base and operating rate by
the applicable rentable area but because all the leases have different
terms and costs I can't wrap my brain around it.

I tried putting 60 text boxes (12 for each year of a 5 year term)on my
multi page form and then built expressions for each (year 1 sq ft rate
x rentable area, year 2 sq ft rate x rentable area and so on...) . I
used 60 because I need to have 5 years info at my fingertips. Very
rudimentary - not to mention it doesn't work well either. I could
perhaps force it to work if I could enter manual values and override
the calculated controls where necessary but you can't do that. The
biggest hurdle is that I set the years up according to our fiscal year
Apr 1 - Mar 30, but unfortunately none of the 'lease years'
coincide with that timing as we usually enter lease agreements
throughout the year.

Am I making sense to anyone?? Is my question still too broad? Thanks
in advance

 

[Jeff Boyce]

Justine

Since you and I have already pursued this thread to some depth, you are less
likely to get the "eyes & brains" on your question by posting .... way
down here .... at the end the thread. You'll get more responses by creating
a new post.

By the way, starting with the underlying data, not the forms, is critical if
you want to make the best use of Access' features and functions. I'll
recommend that you take a look at Jeff C.'s website on database design:

http://home.bendbroadband.com/conradsystems/accessjunkie.html

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/

Okay - I can see what I am asking is quite broad! I will try to
narrow down my first question. If someone wants to suggest another
group where this would be more appropriate please do so.

I would like to create a form (template) where I can enter lease term
information, for example - lease start date - October 1, 2007 and
lease end date September 30th 2012. I would then like to calculate a
monthly base lease cost and a monthly operating lease cost based on
rentable area x applicable sq ft rates for base and operating. If all
leases had the same terms I could do it in a minute with a query or
calculated controls by simply multiplying base and operating rate by
the applicable rentable area but because all the leases have different
terms and costs I can't wrap my brain around it.

I tried putting 60 text boxes (12 for each year of a 5 year term)on my
multi page form and then built expressions for each (year 1 sq ft rate
x rentable area, year 2 sq ft rate x rentable area and so on...) . I
used 60 because I need to have 5 years info at my fingertips. Very
rudimentary - not to mention it doesn't work well either. I could
perhaps force it to work if I could enter manual values and override
the calculated controls where necessary but you can't do that. The
biggest hurdle is that I set the years up according to our fiscal year
Apr 1 - Mar 30, but unfortunately none of the 'lease years'
coincide with that timing as we usually enter lease agreements
throughout the year.

Am I making sense to anyone?? Is my question still too broad? Thanks
in advance

text -- Show quoted text -- Hide quoted text -- Show quoted text -

 

[Larry Linson]

Some time ago (1995 - 2000), I was one of a team which worked on a corporate
real estate application that tracked lease-from, lease-to, and purchase and
sale of owned properties. It had an Access client application to a server
Database (and was client-proprietary, so I have no copy, and, if I did,
could not share).

When I last worked on it, to my knowledge the company had invested over half
a million US$ in it, and "no telling how much more" that I didn't know
about. I'm not trying to scare you off, but to let you know that even just
one of the leasing functions would not be a "snap your fingers and it
springs to life full-grown" kind of project.

Its purpose was to expedite communication between the corporations' real
estate department and the three very large commercial realty firms to whom
they had outsourced purchase, sale, and leasing functions.

Larry Linson
Microsoft Access MVP

 

[David W. Fenton]

I hadn't thought of a college/university - I may end up looking
into that

I wouldn't recommend that. You're likely to end up with people who
have the overview from 35,000 feet but have no real perspective on
what they are seeing -- they own't be able to tell if those are ants
down there or people. I've had experience with college-student-built
applications and they are often really terrible, especially when
done by "genius" CS majors. You want someone experienced with
building databases, and that's not likely to be a college student,
who is more likely to be a code jockey than a database guru.

Also keep in mind that Java is the language of choice these days,
and the programming environment is often web-based. My experience
with databases built by programmers in that environment is that they
are hideously badly designed, with very flat table structures,
horrid duplication of data, no understanding of data validation or
relational integrity, and poor choice of data types.

I don't know where you can find a consultant, but educational
institutions are the last place I'd go for someone who can teach you
how to use Access.