Use one field to automatically determine data in another field

I'm sorry if this is confusing, but I am relatively new to Access and I can't
figure this out through Help.

I'm creating a museum database with data entered through a form. The
pertinent information for this question are fields "Collections", "Date
Entered", "Session #", and "Item #". Each entry can be from only 1
collection. Each collection can only have 1 session per day, but does won't
have one every day. Each Item # is dependent on the session (i.e. Item# 6
from Session 2). There are duplicates of every field because every collection
has multiple entries, there are multiple entries each day, the Session #s
begin at 1 for each Collection and the Item #s begin at 1 for each Session.

What I need it to do is see what Collection it is, then: If the most recent
Session was from the current date, Session # stays the same and Item # is
plus 1. If the most recent session was not the current date, Session # is
plus 1 and Item# is 1. Then I need these results to automatically be entered
and stored through the form. Is this possible?

I've tried queries, filters and tried to under stand the Iif and Lookup
functions and I am lost.
Thanks for any help in advance.
-Ben Banyas

There is no need for duplicates or multiple entries. Are you sure your
tables are properly normalised? Each table should contain unique information.

"benbanyas" wrote:

I'm sorry if this is confusing, but I am relatively new to Access and I can't
figure this out through Help.

I'm creating a museum database with data entered through a form. The
pertinent information for this question are fields "Collections", "Date
Entered", "Session #", and "Item #". Each entry can be from only 1
collection. Each collection can only have 1 session per day, but does won't
have one every day. Each Item # is dependent on the session (i.e. Item# 6
from Session 2). There are duplicates of every field because every collection
has multiple entries, there are multiple entries each day, the Session #s
begin at 1 for each Collection and the Item #s begin at 1 for each Session.

What I need it to do is see what Collection it is, then: If the most recent
Session was from the current date, Session # stays the same and Item # is
plus 1. If the most recent session was not the current date, Session # is
plus 1 and Item# is 1. Then I need these results to automatically be entered
and stored through the form. Is this possible?

I've tried queries, filters and tried to under stand the Iif and Lookup
functions and I am lost.
Thanks for any help in advance.
-Ben Banyas

As scubadiver's response indicates, there's rarely a need to duplicate data
in rows in a well-normalized relational database. If these terms
("relational", "normalized") aren't familiar, you probably have a bit of a
learning curve before you can make good use of the features/functions Access
offers.

Consider posting a description of your table structure to the tablesdbdesign
newsgroup for feedback on how to normalize your data.

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP
http://mvp.support.microsoft.com/

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

"benbanyas" wrote in message
...
I'm sorry if this is confusing, but I am relatively new to Access and I
can't
figure this out through Help.

I'm creating a museum database with data entered through a form. The
pertinent information for this question are fields "Collections", "Date
Entered", "Session #", and "Item #". Each entry can be from only 1
collection. Each collection can only have 1 session per day, but does
won't
have one every day. Each Item # is dependent on the session (i.e. Item# 6
from Session 2). There are duplicates of every field because every
collection
has multiple entries, there are multiple entries each day, the Session #s
begin at 1 for each Collection and the Item #s begin at 1 for each
Session.

What I need it to do is see what Collection it is, then: If the most
recent
Session was from the current date, Session # stays the same and Item # is
plus 1. If the most recent session was not the current date, Session # is
plus 1 and Item# is 1. Then I need these results to automatically be
entered
and stored through the form. Is this possible?

I've tried queries, filters and tried to under stand the Iif and Lookup
functions and I am lost.
Thanks for any help in advance.
-Ben Banyas