Data Fields

[Wendy]

I am trying to set up a database for multiple projects. One of the fields
can have multiple entries (Year format). When I try to query for one year,
the results don't include any of the projects with multiple entries. How can
i make this work? Do I need to make a separate field for each year?

 

[KARL DEWEY]

No, you need a separate record for each year by using two tables in a
one-to-many relationship.
I do not know what else your business requirements are but you need one
table for Projects and then another one for your multi-year details.
Create a primary key in the Project table (an autonumber field works well)
and then a matching foreign key field in the multi-year details table. If
you use the autonumber in Project table then use Number - Long Integer as
foreign key in the details. Open the relationship window, select both
tables, click and drag from autonumber to the long integer, select
Referential Integrity and Cascade Update.
Use a form/subform for Project/details data entry, viewing, and editing.
set the Master/Child links using the autonumber field.

 

[Keith Wilby]

I am trying to set up a database for multiple projects. One of the fields
can have multiple entries (Year format). When I try to query for one
year,
the results don't include any of the projects with multiple entries. How
can
i make this work? Do I need to make a separate field for each year?

No, you need a separate table because if you have more than one date to
record then you have a 1 to many relationship (each record in your main
table can have many dates).

I'd suggest Googling for "one-to-many" and reading up, I'm sure you'll get
some helpful hits.

Regards,
Keith.
www.keithwilby.co.uk

 

[Larry Daugherty]

You're being led astray by MS! Don't use multivalue fields. You
didn't mention them but also don't use Lookup Fields in tables.

Tables hold records of entities. A date is rarely an entity. More
commonly, dates show up as attributes. So don't create "date" based
tables. You are interested in dates but only in the context of an
entity: such as *when* did something happen to *what*. You are more
likely tracking the What.

Your schema (tables and relationships) should reflect the real world
enterprise or thing that you're trying to model and track.

A list of Access resources cribbed from MVP John Vinson follows.
It's huge and intimidating but pick something easy and get started.


Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

Roger Carlson's tutorials, samples and tips:
http://www.rogersaccesslibrary.com/

A free tutorial written by Crystal:
http://allenbrowne.com/casu-22.html

A video how-to series by Crystal:
http://www.YouTube.com/user/LearnAccessByCrystal

MVP Allen Browne's tutorials:
http://allenbrowne.com/links.html#Tutorials

HTH