Linking table Date fields by month

wintermute · April 2nd, 2008, 06:35 AM

Hello,

I have a database design problem that I cannot wrap my head around. There are three tables roughly like this:

Code:

tblUsers      tblAccuracy      tblRecords
-----         ---------        -------
UserID        UserID           UserID
Name          MonthYear        Date
...           Accuracy         NoOfRecords

tblUsers and tblRecords are joined by UserID (Text) in a one-to-many relationship.

tblUsers and tblAccuracy are joined by UserID (Text) in a one-to-many relationship.

tblAccuracy stores User accuracies on a monthly basis where [MonthYear] is a Date/Time column formatted to "mmm yyyy" with each entry being the first of each month (eg. 01/01/2008 to display Jan 2008)

tblRecords stores [NoOfRecords] on a daily basis for each [UserID] where tblRecords.[Date] is a Date/Time column.

---

The problem is that I need a way to query these tables to return a [UserID] from tblUsers, the list of all records entered by that [UserID] for a given month on a daily basis from tblRecords and a single [Accuracy] figure for that [UserID], for that [MonthYear] from tblAccuracy.

tblAccuracy and tblRecords have no primary keys and I'm not sure how to normalize them, or if it is even possible. [MonthYear] is stored eg. as 01/01/2008 for January and [Date] has many records for January and I don't know how to relate them.

I hope someone here can help :S

mmcdonal · April 2nd, 2008, 07:11 AM

I am working on this, but how do you store a date in this format "mmm yyyy"?

mmcdonal

Look it up at: http://wrox.books24x7.com

mmcdonal · April 2nd, 2008, 07:21 AM

Another design issue. Here is what I did:

Here is my basic table data:

tblUsers
UserID Name
1 Mike
2 Calvin
3 Sam

tblRecords
UserID Date NoOfRecords
Calvin 4/2/2008 2
Calvin 4/2/2008 5
Mike 4/2/2008 1
Sam 4/2/2008 3
Sam 4/2/2008 7

tblAccuracy
UserID MonthYear Accuracy
Mike 4/2/2008 2
Calvin 4/2/2008 3
Sam 4/2/2008 1

Date issues notwithstanding, I created to queries to compile the data:

qryRecords
SELECT tblUsers.UserID, tblUsers.Name, Sum(tblRecords.NoOfRecords) AS SumOfNoOfRecords, DatePart("m",[tblRecords.Date]) AS RunMonth
FROM tblUsers LEFT JOIN tblRecords ON tblUsers.UserID = tblRecords.UserID
GROUP BY tblUsers.UserID, tblUsers.Name, DatePart("m",[tblRecords.Date])
HAVING (((DatePart("m",[tblRecords.Date]))=4));

Results:
UserID Name SumOfNoOfRecords RunMonth
1 Mike 1 4
2 Calvin 7 4
3 Sam 10 4

qryAccuracy
SELECT tblUsers.UserID, tblUsers.Name, tblAccuracy.Accuracy, DatePart("m",[MonthYear]) AS RunMonth
FROM tblUsers LEFT JOIN tblAccuracy ON tblUsers.UserID = tblAccuracy.UserID
GROUP BY tblUsers.UserID, tblUsers.Name, tblAccuracy.Accuracy, DatePart("m",[MonthYear])
HAVING (((DatePart("m",[MonthYear]))=4));

Results:
UserID Name Accuracy RunMonth
1 Mike 2 4
2 Calvin 3 4
3 Sam 1 4

Then I created a query called "qryRecords_Accuracy"

qryRecords_Accuracy
SELECT qryRecords.Name, qryRecords.SumOfNoOfRecords, qryAccuracy.Accuracy
FROM qryRecords INNER JOIN qryAccuracy ON qryRecords.UserID = qryAccuracy.UserID;

Results:
Name SumOfNoOfRecords Accuracy
Mike 1 2
Calvin 7 3
Sam 10 1

Did that help?

mmcdonal

Look it up at: http://wrox.books24x7.com

mmcdonal · April 2nd, 2008, 07:30 AM

I didn't mention. I coded in the Month to be 4, but you can either pass that parameter when you open the query, or you can leave it blank and get all the records by Month for each user. The problem with the last method is that it will compile one year on top of the next unless you also specify DatePart("yyyy", [Date])

Also, I see a lot of people doing this, but there are reserved names in SQL that you don't want to use for column names, like DATE.

mmcdonal

Look it up at: http://wrox.books24x7.com

wintermute · April 3rd, 2008, 04:35 AM

Hello, thanks for taking an interest.

I have since with help from another forum solved this problem quite elegantly like this:

Code:

SELECT *
FROM tblAccuracy INNER JOIN tblRecords
     ON tblAccuracy.UserID = tblRecords.UserID
     WHERE MonthYear = getDateMonth()
     AND (tblRecords.vDate BETWEEN getDateMonth() AND getDateSerial())

Where vDate is the changed Date as you also pointed out, and the VBA functions are very simple and just pass values from a form.
The only one slightly out is:

Code:

Public Function getDateSerial() As Date

    Dim c As Control
    Set c = Forms![frmBonusReport]![cbDateMonth]
    getDateSerial = DateSerial(Year(c.Value), Month(c.Value) + 1, 0)

End Function

Still, since you put a lot of thought to helping me out, I will time permitting today follow through with the design you suggested and let you know if it also works :)

Thanks again.