A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Data Fields



 
 
Thread Tools Display Modes
  #1  
Old July 6th, 2009, 04:08 PM posted to microsoft.public.access.tablesdbdesign
Wendy
external usenet poster
 
Posts: 255
Default Data Fields

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?
  #2  
Old July 6th, 2009, 04:53 PM posted to microsoft.public.access.tablesdbdesign
KARL DEWEY
external usenet poster
 
Posts: 10,767
Default Data Fields

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.

"Wendy" wrote:

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?

  #3  
Old July 6th, 2009, 05:00 PM posted to microsoft.public.access.tablesdbdesign
Keith Wilby
external usenet poster
 
Posts: 812
Default Data Fields

"Wendy" wrote in message
...

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

  #4  
Old July 7th, 2009, 01:09 AM posted to microsoft.public.access.tablesdbdesign
Larry Daugherty
external usenet poster
 
Posts: 1,012
Default Data Fields

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/acc...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
--
-Larry-
--

"Wendy" wrote in message
...
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?



 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 03:27 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.