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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|