Table Design and Relationship 1

[Wes1961]

I want to create a table that will be used in creating a "SnapShot" view of reports during a nightly update process and store them on the server. I want to do this so that users can have access to the reports without distributing paper copies. Also, I do not want everyone that needs the reports to have access to the program.

The first table I want to create is table that contains the reports to be created. I want this table so that Production Control people can add reports without the involvement of programming.

Table 1 - tblReportDetails
Num rptName Desc Location
1 rptPastDue Past Due Product m:\dir1\reports
2 rptOpenOrd Open Orders m:\customerservice\
3 rptNewJobs New Jobs m:\dir1\reports
4 .....
5 ..
6 ...


Next I want to be able to attach and email select reports indicated by a second table.

Table 2 - tblReportEmail
email Report
user1@domain.com 1,3,4,etc


The real crux of my problem is how do I design and link these tables. If user1 gets reports 1,3,4,7. I really don't want to enter the info multiple times like:
user1@domain.com 1
user1@domain.com 3
user1@domain.com 4
user1@domain.com 7
user2@domain.com 1

Now user2 may get reports 1,4,9 and so forth.

Can someone give me some hints or help as how to design and link these tables?

Thanks in advance!

 

[KenReay]

Hi

I Think you need three tables here

tblReports
ReportId (PK)
ReportDesc
ReportLocation

tblUsers
UserId (PK)
UserEmailAddress

tblReportDistribution
ReportId )PK

By making a select query joining tblReports to tblReportDistribution on ReportId, and tblReportDistribution to tblUsers on UserId, you will get a complete list of reprots to be EMailed and to whom tehy must be EMailed
UserId )

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[lespaul]

I really don't want to enter the info multiple times like:

why not? that's the basis for a relational database?

'The Fundamentals of Relational Database Design'

leslie

 

[willir]

Hmmm

In addition to KenReay's post, you can store "profiles" for each targeted recipeint so they would get the "subscribed" set of reports. I think this is what you meant by

I really don't want to enter the info multiple times like:

This is really a many-to-many table...
- one person may get many reports
- one report may be sent to many people

tblSubscription
UserId - foreign key to User table
ReportId - foreign key to Report table
ExpireDate

(Ken, is this what you meant for ...
tblReportDistribution
ReportId )PK )

(It almost looks like your post got messed at this point)

You then setup the profiles for users and their reports. You would used this table as the "driver" file when generating your mailouts.

Richyard

 

[KenReay]

Hi
Yes exactly

tblReportDistribution
ReportId )PK

By making a select query joining tblReports to tblReportDistribution on ReportId, and tblReportDistribution to tblUsers on UserId, you will get a complete list of reprots to be EMailed and to whom tehy must be EMailed
UserId )

should have said

tblReportDistribution
ReportId )PK
UserId )

By making a select query joining tblReports to tblReportDistribution on ReportId, and tblReportDistribution to tblUsers on UserId, you will get a complete list of reprots to be EMailed and to whom tehy must be EMailed

Do not know what caused that glitch, even my typing is not that bad!


Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[Wes1961]

Sorry for sounding dumb when I said :
" I really don't want to enter the info multiple times like:"

What I meant was that I didn't want to type "u.s.e.r.@.d...." everytime. Not that I didn't want multiple records. Bad choice of wording.

Thanks Ken you first selection was just what I needed to clear the haze away and give me what I needed. Well, after I finally figured out that you left out a field in the last table .. lol. I already gave you a star.