Update field and other problems

  • Thread starter Thread starter Lee Grant
  • Start date Start date
L

Lee Grant

Hello folks,

I'm in a pickle and it's because I'm not great at Access. I have a stack of
books here and I cannot seem to find my way around a few problems.

I have an existing 'database' system using Excel but I thought it was time
to grow up and learn how to use Access...

I have three populated tables:

Table 1 - Runner Information

RunnerID (PK)
firstname
surname
dob
sex
address1
address2
address3
town/city
county
landline
mobile
email

Table 2 - Race_Details

Race_Details_ID (PK)
EventID
racenumber
ageonraceday
category
dateofentry
lap1
lap2
total
status
source
tshirtsize
RunnerID
Fee
paymenttype
chequenumber
cashed_batch
ssaeenc
emailsent
informationposted
phonecallmade

Table 3 - Event

EventID (PK)
dateofevent
nameofevent

These fields have been populated with data from the Excel sheets. In the
Excel versions, a lot of the data was calculated and then stored and I need
to do the same in Access. I have created a Form that displays the content
of the tables but I need to add the calculations to:

1) Take the 'dob' field from the Runner Information table and the
'dateofevent' from the Event table and populate the 'ageonraceday' from the
'Race Details' table - I was given some help on this one earlier this week
but the database has been changed and it now no longer works :-(
2) I then want to take the 'sex' from the 'Runner Information' table and the
'ageonraceday' from the 'Race Details' table and use it to calculate the
'category' field in the 'Race Details' table. The categories are such:
Males less than 35 are categorised as 'M'
Males between 35 and 39 are categorised as 'M35'
Males between 40 and 44 are categorised as 'M40'
Males between 45 and 49 are categorised as 'M45'
Males between 50 and 54 are categorised as 'M50'
Males between 55 and 59 are categorised as 'M55'
Males between 60 and 64 are categorised as 'M60'
Males between 65 and 69 are categorised as 'M65'
Males between 70 and 74 are categorised as 'M70'
Males between 75 and 79 are categorised as 'M75'

The females are categorised that same except they are prefixed 'F'
3) Finally, when I enter new information into the 'Race Details' table, I
need to ensure that the 'racenumber' value is unique, sequential for all new
records that have the EventID of '2009'.

I know this is a lot of requests. I cannot code for toffee but I can write
a mean excel formula. I think that is some of the problem - I'm thinking
Excel and not managing to think Access.

Any help would really be appreciated.


Kindest Regards,

Lee
 
Hello folks,

I'm in a pickle and it's because I'm not great at Access. I have a stack of
books here and I cannot seem to find my way around a few problems.

I have an existing 'database' system using Excel but I thought it was time
to grow up and learn how to use Access...
Click to expand...

Just be aware that they are VERY DIFFERENT PROGRAMS and that you'll probably
need to "unlearn" some of "the way things are always done". If you're cool
with that, great...
I have three populated tables:

Table 1 - Runner Information

RunnerID (PK)
firstname
surname
dob
sex
address1
address2
address3
town/city
county
landline
mobile
email
Click to expand...

looks good...
Table 2 - Race_Details

Race_Details_ID (PK)
EventID
racenumber
ageonraceday
category
dateofentry
lap1
lap2
total
status
source
tshirtsize
RunnerID
Fee
paymenttype
chequenumber
cashed_batch
ssaeenc
emailsent
informationposted
phonecallmade

Table 3 - Event

EventID (PK)
dateofevent
nameofevent

These fields have been populated with data from the Excel sheets. In the
Excel versions, a lot of the data was calculated and then stored and I need
to do the same in Access. I have created a Form that displays the content
of the tables but I need to add the calculations to:
Click to expand...

That's one of the differences. In Excel you'll calculate and store - or in
reality, use an expression in a cell to calculate on the fly.

In Access you would store only "hard data" - date of birth, date of the race,
etc.; fields like "ageonraceday" would simply NOT EXIST in your table, period.
Instead you would use a Query to calculate it dynamically, on the fly.

If you're using Access Tables to interact with data - as you would with a
spreadsheet - *don't*. Tables are for storage of hard data, and that's IT.
They're neither designed nor appropriate for data display or data editing.
You'ld use a Form for that.
1) Take the 'dob' field from the Runner Information table and the
'dateofevent' from the Event table and populate the 'ageonraceday' from the
'Race Details' table - I was given some help on this one earlier this week
but the database has been changed and it now no longer works :-(
Click to expand...

Your Race Details table has the RunnerID (who ran) and EventID (what did they
run) as links to the Runners and Events tables. You would create a Query
joining all three tables, and use a calculated field:

AgeOnRaceDay: DateDiff("yyyy", [RunnerInformation].[dob],
[Event].[DateOfEvent]) - IIF(Format( [RunnerInformation].[dob],"mmdd") >
Format([Event].[DateOfEvent], "mmdd"), 1, 0)

to calculate (dynamically) the age as of most recent birthday.

2) I then want to take the 'sex' from the 'Runner Information' table and the
'ageonraceday' from the 'Race Details' table and use it to calculate the
'category' field in the 'Race Details' table. The categories are such:
Males less than 35 are categorised as 'M'
Males between 35 and 39 are categorised as 'M35'
Males between 40 and 44 are categorised as 'M40'
Males between 45 and 49 are categorised as 'M45'
Males between 50 and 54 are categorised as 'M50'
Males between 55 and 59 are categorised as 'M55'
Males between 60 and 64 are categorised as 'M60'
Males between 65 and 69 are categorised as 'M65'
Males between 70 and 74 are categorised as 'M70'
Males between 75 and 79 are categorised as 'M75'

The females are categorised that same except they are prefixed 'F'
Click to expand...

Create a new table Categories with fields Sex, Age, and Category. Join this
into your report query joining on the calculated AgeOnRaceDay field and the
Sex field from the runner information.
3) Finally, when I enter new information into the 'Race Details' table, I
need to ensure that the 'racenumber' value is unique, sequential for all new
records that have the EventID of '2009'.
Click to expand...

You'll need some code to do that, and will need to do your entry using a Form
(you can't do it in a table).
I know this is a lot of requests. I cannot code for toffee but I can write
a mean excel formula. I think that is some of the problem - I'm thinking
Excel and not managing to think Access.
Click to expand...

So... STOP THAT!!! <g>

We'll help.
 
Thanks John,

I'm really encouraged by your comments. I'll work on your suggestions over
the weekend and let you know how I get on.

I do really appreciate your time. Thank you.

Lee

John W. Vinson said:
Hello folks,

I'm in a pickle and it's because I'm not great at Access. I have a stack
of
books here and I cannot seem to find my way around a few problems.

I have an existing 'database' system using Excel but I thought it was time
to grow up and learn how to use Access...
Click to expand...

Just be aware that they are VERY DIFFERENT PROGRAMS and that you'll
probably
need to "unlearn" some of "the way things are always done". If you're cool
with that, great...
I have three populated tables:

Table 1 - Runner Information

RunnerID (PK)
firstname
surname
dob
sex
address1
address2
address3
town/city
county
landline
mobile
email
Click to expand...

looks good...
Table 2 - Race_Details

Race_Details_ID (PK)
EventID
racenumber
ageonraceday
category
dateofentry
lap1
lap2
total
status
source
tshirtsize
RunnerID
Fee
paymenttype
chequenumber
cashed_batch
ssaeenc
emailsent
informationposted
phonecallmade

Table 3 - Event

EventID (PK)
dateofevent
nameofevent

These fields have been populated with data from the Excel sheets. In the
Excel versions, a lot of the data was calculated and then stored and I
need
to do the same in Access. I have created a Form that displays the content
of the tables but I need to add the calculations to:
Click to expand...

That's one of the differences. In Excel you'll calculate and store - or in
reality, use an expression in a cell to calculate on the fly.

In Access you would store only "hard data" - date of birth, date of the
race,
etc.; fields like "ageonraceday" would simply NOT EXIST in your table,
period.
Instead you would use a Query to calculate it dynamically, on the fly.

If you're using Access Tables to interact with data - as you would with a
spreadsheet - *don't*. Tables are for storage of hard data, and that's IT.
They're neither designed nor appropriate for data display or data editing.
You'ld use a Form for that.
1) Take the 'dob' field from the Runner Information table and the
'dateofevent' from the Event table and populate the 'ageonraceday' from
the
'Race Details' table - I was given some help on this one earlier this week
but the database has been changed and it now no longer works :-(
Click to expand...

Your Race Details table has the RunnerID (who ran) and EventID (what did
they
run) as links to the Runners and Events tables. You would create a Query
joining all three tables, and use a calculated field:

AgeOnRaceDay: DateDiff("yyyy", [RunnerInformation].[dob],
[Event].[DateOfEvent]) - IIF(Format( [RunnerInformation].[dob],"mmdd") >
Format([Event].[DateOfEvent], "mmdd"), 1, 0)

to calculate (dynamically) the age as of most recent birthday.

2) I then want to take the 'sex' from the 'Runner Information' table and
the
'ageonraceday' from the 'Race Details' table and use it to calculate the
'category' field in the 'Race Details' table. The categories are such:
Males less than 35 are categorised as 'M'
Males between 35 and 39 are categorised as 'M35'
Males between 40 and 44 are categorised as 'M40'
Males between 45 and 49 are categorised as 'M45'
Males between 50 and 54 are categorised as 'M50'
Males between 55 and 59 are categorised as 'M55'
Males between 60 and 64 are categorised as 'M60'
Males between 65 and 69 are categorised as 'M65'
Males between 70 and 74 are categorised as 'M70'
Males between 75 and 79 are categorised as 'M75'

The females are categorised that same except they are prefixed 'F'
Click to expand...

Create a new table Categories with fields Sex, Age, and Category. Join
this
into your report query joining on the calculated AgeOnRaceDay field and
the
Sex field from the runner information.
3) Finally, when I enter new information into the 'Race Details' table, I
need to ensure that the 'racenumber' value is unique, sequential for all
new
records that have the EventID of '2009'.
Click to expand...

You'll need some code to do that, and will need to do your entry using a
Form
(you can't do it in a table).
I know this is a lot of requests. I cannot code for toffee but I can
write
a mean excel formula. I think that is some of the problem - I'm thinking
Excel and not managing to think Access.
Click to expand...

So... STOP THAT!!! <g>

We'll help.
Click to expand...
 
Hi John,

I've just tried the solutions. I understand what you're saying about the
differences between Excel & Access. I am used to the Excel way of working
and seeing calculated data appear before my very eyes. It is very
reassuring to see this information but if this is not best practice then I
need to get my head into a new way of working.

I'll ditch some of my 'calculated' fields and try to recreate the values 'on
the fly'.

Anyway, I have a couple of questions which I've slotted in below:


Lee Grant said:
Thanks John,

I'm really encouraged by your comments. I'll work on your suggestions
over the weekend and let you know how I get on.

I do really appreciate your time. Thank you.

Lee

John W. Vinson said:
Hello folks,

I'm in a pickle and it's because I'm not great at Access. I have a stack
of
books here and I cannot seem to find my way around a few problems.

I have an existing 'database' system using Excel but I thought it was
time
to grow up and learn how to use Access...
Click to expand...

Just be aware that they are VERY DIFFERENT PROGRAMS and that you'll
probably
need to "unlearn" some of "the way things are always done". If you're
cool
with that, great...
I have three populated tables:

Table 1 - Runner Information

RunnerID (PK)
firstname
surname
dob
sex
address1
address2
address3
town/city
county
landline
mobile
email
Click to expand...

looks good...
Table 2 - Race_Details

Race_Details_ID (PK)
EventID
racenumber
ageonraceday
category
dateofentry
lap1
lap2
total
status
source
tshirtsize
RunnerID
Fee
paymenttype
chequenumber
cashed_batch
ssaeenc
emailsent
informationposted
phonecallmade

Table 3 - Event

EventID (PK)
dateofevent
nameofevent

These fields have been populated with data from the Excel sheets. In the
Excel versions, a lot of the data was calculated and then stored and I
need
to do the same in Access. I have created a Form that displays the
content
of the tables but I need to add the calculations to:
Click to expand...

That's one of the differences. In Excel you'll calculate and store - or
in
reality, use an expression in a cell to calculate on the fly.

In Access you would store only "hard data" - date of birth, date of the
race,
etc.; fields like "ageonraceday" would simply NOT EXIST in your table,
period.
Instead you would use a Query to calculate it dynamically, on the fly.

If you're using Access Tables to interact with data - as you would with
a
spreadsheet - *don't*. Tables are for storage of hard data, and that's
IT.
They're neither designed nor appropriate for data display or data
editing.
You'ld use a Form for that.
1) Take the 'dob' field from the Runner Information table and the
'dateofevent' from the Event table and populate the 'ageonraceday' from
the
'Race Details' table - I was given some help on this one earlier this
week
but the database has been changed and it now no longer works :-(
Click to expand...

Your Race Details table has the RunnerID (who ran) and EventID (what did
they
run) as links to the Runners and Events tables. You would create a Query
joining all three tables, and use a calculated field:

AgeOnRaceDay: DateDiff("yyyy", [RunnerInformation].[dob],
[Event].[DateOfEvent]) - IIF(Format( [RunnerInformation].[dob],"mmdd") >
Format([Event].[DateOfEvent], "mmdd"), 1, 0)

to calculate (dynamically) the age as of most recent birthday.
Click to expand...
Click to expand...

This worked fine. I created a query and used the 'zoom' function to input
for formula. The query ran and there was the age - excellent - thank you.

I think I understand. I created a new table (called Categories) as you said
and populated it with the data. Example:

Sex Age Category
Male 17 M
Male 18 M
Male 19 M

and so on...

Female 37 F35
Female 38 F35

etc.

All the categories entered.

I think this is what you meant.

The problem that I have now is that I want to run query that will give me
the category for a runner:

My query has the following fields:

Event.EventID (Criteria 2009)
Runner Information.firstname
Runner Information.surname
Runner Information.dob
Runner Information.sex
Event.dateofevent

I then use the zoom to enter the formula you gave me to calculate
ageonraceday. This is displayed in the query design as:

Expr1: DateDiff("yyyy",[Runner
Information].[dob],[Event].[DateOfEvent])-IIf(Format([Runner
Information].[dob],"mmdd")>Format([Event].[DateOfEvent],"mmdd"),1,0)

Finally, I add

Categories.category

I know I need to add some criteria but I'm unsure of the formula - this is
what I tried (and failed!):

[Categories]![sex]=[Runner Information]![sex] And [Categories]![age]=[Expr1]

As you can see, when the query runs, I want it to pull the correct catergory
out of the Categories table. I assuming I need to get it match 'sex' from
both tables (the Categories & Runner Information) and the 'age' (from
Categories and the calculated Expr1). I think my problem is to do with the
Expr1 and the syntax I'm using.

Can you see the problem?

No problem. I'll sort the above problem first (and some others) before I
tackle this one!!
So... STOP THAT!!! <g>

We'll help.
Click to expand...

Thanks John. If you could give me another push in the correct direction
then I would be very greatful!

Regards,

Lee
Click to expand...
 
Hi John,

I've just tried the solutions. I understand what you're saying about the
differences between Excel & Access. I am used to the Excel way of working
and seeing calculated data appear before my very eyes. It is very
reassuring to see this information but if this is not best practice then I
need to get my head into a new way of working.
Click to expand...

The information WILL appear "on the fly" before your eyes... if you're looking
in the right place (the query datasheet for debugging, the Form for production
use). You won't need to be looking at the table much at all.
I'll ditch some of my 'calculated' fields and try to recreate the values 'on
the fly'.

Anyway, I have a couple of questions which I've slotted in below:
Click to expand...

This worked fine. I created a query and used the 'zoom' function to input
for formula. The query ran and there was the age - excellent - thank you.
Click to expand...

I think I understand. I created a new table (called Categories) as you said
and populated it with the data. Example:

Sex Age Category
Male 17 M
Male 18 M
Male 19 M

and so on...

Female 37 F35
Female 38 F35

etc.

All the categories entered.

I think this is what you meant.
Yep

The problem that I have now is that I want to run query that will give me
the category for a runner:

My query has the following fields:

Event.EventID (Criteria 2009)
Runner Information.firstname
Runner Information.surname
Runner Information.dob
Runner Information.sex
Event.dateofevent

I then use the zoom to enter the formula you gave me to calculate
ageonraceday. This is displayed in the query design as:

Expr1: DateDiff("yyyy",[Runner
Information].[dob],[Event].[DateOfEvent])-IIf(Format([Runner
Information].[dob],"mmdd")>Format([Event].[DateOfEvent],"mmdd"),1,0)

Finally, I add

Categories.category

I know I need to add some criteria but I'm unsure of the formula - this is
what I tried (and failed!):

[Categories]![sex]=[Runner Information]![sex] And [Categories]![age]=[Expr1]
Click to expand...

Replace [expr1] with the entire DateDiff expression. You can't reuse a
calculated field in a further calculation (most of the time anyway). Don't
worry, the query analyzer is smart enough to call the function only once.
As you can see, when the query runs, I want it to pull the correct catergory
out of the Categories table. I assuming I need to get it match 'sex' from
both tables (the Categories & Runner Information) and the 'age' (from
Categories and the calculated Expr1). I think my problem is to do with the
Expr1 and the syntax I'm using.
Click to expand...

Thanks John. If you could give me another push in the correct direction
then I would be very greatful!
Click to expand...

hmmm... a nice piece of toffee would go well with this coffee...

Good luck and don't hesitate to post back!
 
Yeh!

Thank you, thank you, thank you!

At first I got a 'type mismatch' which I cured by realising I'd got my data
types in the new table wrong (numbers as text!!).

I can now go to bed and sleep peacefully!

You've been brilliant!

Regards,

Lee

John W. Vinson said:
Hi John,

I've just tried the solutions. I understand what you're saying about the
differences between Excel & Access. I am used to the Excel way of working
and seeing calculated data appear before my very eyes. It is very
reassuring to see this information but if this is not best practice then
I
need to get my head into a new way of working.
Click to expand...

The information WILL appear "on the fly" before your eyes... if you're
looking
in the right place (the query datasheet for debugging, the Form for
production
use). You won't need to be looking at the table much at all.
I'll ditch some of my 'calculated' fields and try to recreate the values
'on
the fly'.

Anyway, I have a couple of questions which I've slotted in below:
Click to expand...

This worked fine. I created a query and used the 'zoom' function to input
for formula. The query ran and there was the age - excellent - thank you.
Click to expand...

I think I understand. I created a new table (called Categories) as you
said
and populated it with the data. Example:

Sex Age Category
Male 17 M
Male 18 M
Male 19 M

and so on...

Female 37 F35
Female 38 F35

etc.

All the categories entered.

I think this is what you meant.
Yep

The problem that I have now is that I want to run query that will give me
the category for a runner:

My query has the following fields:

Event.EventID (Criteria 2009)
Runner Information.firstname
Runner Information.surname
Runner Information.dob
Runner Information.sex
Event.dateofevent

I then use the zoom to enter the formula you gave me to calculate
ageonraceday. This is displayed in the query design as:

Expr1: DateDiff("yyyy",[Runner
Information].[dob],[Event].[DateOfEvent])-IIf(Format([Runner
Information].[dob],"mmdd")>Format([Event].[DateOfEvent],"mmdd"),1,0)

Finally, I add

Categories.category

I know I need to add some criteria but I'm unsure of the formula - this is
what I tried (and failed!):

[Categories]![sex]=[Runner Information]![sex] And
[Categories]![age]=[Expr1]
Click to expand...

Replace [expr1] with the entire DateDiff expression. You can't reuse a
calculated field in a further calculation (most of the time anyway). Don't
worry, the query analyzer is smart enough to call the function only once.
As you can see, when the query runs, I want it to pull the correct
catergory
out of the Categories table. I assuming I need to get it match 'sex' from
both tables (the Categories & Runner Information) and the 'age' (from
Categories and the calculated Expr1). I think my problem is to do with
the
Expr1 and the syntax I'm using.
Click to expand...

Thanks John. If you could give me another push in the correct direction
then I would be very greatful!
Click to expand...

hmmm... a nice piece of toffee would go well with this coffee...

Good luck and don't hesitate to post back!
Click to expand...
 
At first I got a 'type mismatch' which I cured by realising I'd got my data
types in the new table wrong (numbers as text!!).

I can now go to bed and sleep peacefully!
Click to expand...

Glad to have been of assistance.
 
Back
Top