Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
| Search | Today's Posts | Mark Forums Read
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old July 7th, 2004, 04:55 AM
Authorized User
Join Date: Mar 2004
Location: , , .
Posts: 11
Thanks: 0
Thanked 0 Times in 0 Posts
Default How to deal with relationships?

I've tried, I created two tables, one with Date/Time, Desc, Misc and another with Name and UserID.. Of course the second table will be customized for the user, eg.. only one database per user to keep it simple.

How do I create a report that generates the Date/Time and Desc. and plugs in the Name and userID from the second table? All I get is Name and UserID in the generated report.

Thank you.

  #2 (permalink)  
Old July 7th, 2004, 04:57 AM
Authorized User
Join Date: Mar 2004
Location: , , .
Posts: 11
Thanks: 0
Thanked 0 Times in 0 Posts

What I mean is, the text UserID and Name show up in place instead of the values in the table for UserInfo (I put myself, it will be the only entry).. I am reporting on one table and hoping to get value from second table.


  #3 (permalink)  
Old July 7th, 2004, 05:13 AM
Friend of Wrox
Points: 1,288, Level: 14
Points: 1,288, Level: 14 Points: 1,288, Level: 14 Points: 1,288, Level: 14
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
Join Date: Jun 2004
Location: Stockholm, Sweden
Posts: 331
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to qazi_nomi

Very confusing ! I cant understand what you actually wants to do

Love 4 all
  #4 (permalink)  
Old July 7th, 2004, 07:36 AM
Friend of Wrox
Join Date: Jun 2003
Location: , , USA.
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts

Hi aCK!,

It looks like you're trying to track session info, in which case you need a 1-to-many relationship
established between your User table and your SessionDetails table.

In general, you establish a relationship between two tables by creating a foreign key constraint.

First, both of your tables need primary keys defined.

Second, create a forign key constraint by placing a copy (name and data type) of the primary key field of the table on the "one" side of your relationship (Users) in the table on the many side of your relationship (SessionDetails).

Third, create a join between the primary key field of the Users table and the foreign key field in
the SessionDetails table. You can do that in the Access Relationships window. Just drag-and-drop.

Here's the table structures:

Table: tblUsers

Field: UserID [PrimaryKey]
Type: Number

Field: UserName
Type: Text

Table: tblSessionDetails

Field: SessionID [PrimaryKey]
Type: AutoNumber

Field: UserID [ForeignKey]
Type: Number

Field: SessionDateTime
Type: Date/Time

Field: Desc
Type: Text

Field: Misc
Type: Text

Then to get a resutset of session info, use a query like:

SELECT tblUsers.UserID, tblUsers.UserName, tblSessionDetails.SessionDateTime, tblSessionDetails.Desc, tblSessionDetails.Misc
FROM tblUsers INNER JOIN tblSessionDetails ON tblUsers.UserID = tblSessionDetails.UserID;

Also, avoid using Date/Time as a field name since it is an Access data type name.



Similar Threads
Thread Thread Starter Forum Replies Last Post
How to deal with Session? chill06 ASP.NET 2.0 Professional 0 February 22nd, 2007 12:57 PM
deal with "&" Magen Classic ASP Basics 3 February 6th, 2007 12:09 PM
Deal with multiple sources Hind XSLT 2 June 5th, 2006 07:16 AM
How to deal with the picture?? CoooooL Pro VB Databases 1 May 3rd, 2005 07:26 AM
How to deal with String has ' in SQL Statement chiyahu Classic ASP Databases 1 August 28th, 2003 04:00 PM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.