Function Help For Novice

[Steve Pinn]

OK Folks some help please I’ll own up to being an absolute Access novice before
I start until now I’ve always been able to use Excel to accomplish my limited
database requirements but there comes a time (120MB in this case) when you have
to accept that Excel is not and never will be a real databse.

So I have an Access database comprising of 23 (will be more) linked tables, each
table is identical in format with approx 15,000 records. Each record comprises
of:

Field 1 Date
Field 2 Time
Field 3 Integer
Field 4 Integer
Field 5 Integer
Field 6 Integer

I need to print a report that will provide the following

Number of instances where either field 3 or field 4 exceeds a specified fixed
value (x)

Number of instances where Field 5 exceeds a specified fixed value (y)

Number of instances where Field 5 exceeds the specified value (y) when field 6
is greater than a specified value (z)

As the limits are fixed, I thought I could simply do this with functions on the
report layout but after a frustrating day (and night) I’m not sure.

Can somebody please give me some hints, particular the functions I should use

Thanks and apologies for my incompetence..


Steve Pinn

 

[John Vinson]

OK Folks some help please I’ll own up to being an absolute Access novice before
I start until now I’ve always been able to use Excel to accomplish my limited
database requirements but there comes a time (120MB in this case) when you have
to accept that Excel is not and never will be a real databse.

Excel has never pretended to be a "real database". It's an excellent
spreadsheet, the gold standard in fact. But Access is not a "bigger
faster Excel" - they are VERY DIFFERENT and require different logic.

So I have an Access database comprising of 23 (will be more) linked tables, each
table is identical in format with approx 15,000 records. Each record comprises
of:

In that case your database needs to be completely restructured.
Storing multiple identically structured spreadsheets is typical;
storing data in multiple identically structured tables is *VERY BAD
DESIGN*. You're much better off using *one* large table with an
additional field distinguishing the 23 different sets of data. If you
need to extract the data that was in spreadsheet 18, a simple query
will do so; or you'll be able to easily compare or combine the data
across sets.

Field 1 Date
Field 2 Time
Field 3 Integer
Field 4 Integer
Field 5 Integer
Field 6 Integer

You should consider storing the date and time together in a single
Date/Time field. In addition you will want to have a Primary Key - a
field or combination of fields which uniquely identify which record is
which. In a spreadsheet you have a row number; in Access YOU DO NOT -
tables (again) aren't spreadsheets. They are like a "heap" of data,
and there is no usable "record number" or "previous record" or "next
record" concept. If you want to identify individual records you must
have something (which could be this date/time field, if it's unique)
to specify which record you're talking about.

I need to print a report that will provide the following

Number of instances where either field 3 or field 4 exceeds a specified fixed
value (x)

Easy - just a query with a criterion of > [X] on each field, using OR
logic; make it a Totals query and count records.

Number of instances where Field 5 exceeds a specified fixed value (y)
ditto

Number of instances where Field 5 exceeds the specified value (y) when field 6
is greater than a specified value (z)

Ditto, but using AND logic

As the limits are fixed, I thought I could simply do this with functions on the
report layout but after a frustrating day (and night) I’m not sure.

Report layout is the WRONG PLACE. Use a Query instead, and base your
report on the Query. For the last example, use the query grid. Add
(one of?) your tables; select the eight fields; and on the criteria
line under Field5 put

[Enter Y:]

and under Field6 put

[Enter Z:]

If these are constants that won't change just put them in instead of
the bracketed expressions (which will prompt you for the values): e.g.

100
346

if Y is 100 and Z is 346.

Can somebody please give me some hints, particular the functions I should use

Thanks and apologies for my incompetence..

Lack of knowledge <> incompetence!

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[Steve Pinn]

Thanks for the pointers John though to be honest I'm still not getting far.

One point I understand your comments about multiple tables but in this case all
the "dates and times" are different and there is no single field that would
differentiate the data if I amalgamated it into a single table.

Any chance of some poinetrs in exactly what I should enter into the query I
really am struggling?

Steve

OK Folks some help please I’ll own up to being an absolute Access novice before
I start until now I’ve always been able to use Excel to accomplish my limited
database requirements but there comes a time (120MB in this case) when you have
to accept that Excel is not and never will be a real databse.

Excel has never pretended to be a "real database". It's an excellent
spreadsheet, the gold standard in fact. But Access is not a "bigger
faster Excel" - they are VERY DIFFERENT and require different logic.

So I have an Access database comprising of 23 (will be more) linked tables, each
table is identical in format with approx 15,000 records. Each record comprises
of:

In that case your database needs to be completely restructured.
Storing multiple identically structured spreadsheets is typical;
storing data in multiple identically structured tables is *VERY BAD
DESIGN*. You're much better off using *one* large table with an
additional field distinguishing the 23 different sets of data. If you
need to extract the data that was in spreadsheet 18, a simple query
will do so; or you'll be able to easily compare or combine the data
across sets.

Field 1 Date
Field 2 Time
Field 3 Integer
Field 4 Integer
Field 5 Integer
Field 6 Integer

You should consider storing the date and time together in a single
Date/Time field. In addition you will want to have a Primary Key - a
field or combination of fields which uniquely identify which record is
which. In a spreadsheet you have a row number; in Access YOU DO NOT -
tables (again) aren't spreadsheets. They are like a "heap" of data,
and there is no usable "record number" or "previous record" or "next
record" concept. If you want to identify individual records you must
have something (which could be this date/time field, if it's unique)
to specify which record you're talking about.

I need to print a report that will provide the following

Number of instances where either field 3 or field 4 exceeds a specified fixed
value (x)

Easy - just a query with a criterion of > [X] on each field, using OR
logic; make it a Totals query and count records.

Number of instances where Field 5 exceeds a specified fixed value (y)
ditto

Number of instances where Field 5 exceeds the specified value (y) when field 6
is greater than a specified value (z)

Ditto, but using AND logic

As the limits are fixed, I thought I could simply do this with functions on the
report layout but after a frustrating day (and night) I’m not sure.

Report layout is the WRONG PLACE. Use a Query instead, and base your
report on the Query. For the last example, use the query grid. Add
(one of?) your tables; select the eight fields; and on the criteria
line under Field5 put

[Enter Y:]

and under Field6 put

[Enter Z:]

If these are constants that won't change just put them in instead of
the bracketed expressions (which will prompt you for the values): e.g.

100
346

if Y is 100 and Z is 346.

Can somebody please give me some hints, particular the functions I should use

Thanks and apologies for my incompetence..

Lack of knowledge <> incompetence!

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[John Vinson]

Thanks for the pointers John though to be honest I'm still not getting far.

One point I understand your comments about multiple tables but in this case all
the "dates and times" are different and there is no single field that would
differentiate the data if I amalgamated it into a single table.

Any chance of some poinetrs in exactly what I should enter into the query I
really am struggling?

Without knowing what these "dates and times" are, I'm at rather a loss
at how to advise you. One possible idea is that - if you're recording
dates and times for particular events or deadlines - would be to have
a table of Events. *PURELY HYPOTHETICALLY*, since I don't know what
you're trying to accomplish: if you have a table of People, and a
table of activities that each person needs to perform at a particular
time, you could use a third table with three (or more) fields: the
PersonID (linked to the table of people), and ActivityID (linked to
the table of activities), and the date/time of that activity. You
might need additional fields to describe any information that's needed
about *that* person and *that* activity.


John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps