Linking table Date fields by month

[wintermute]

Hello,

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

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

 

[neileg]

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)

Although you can apply formatting at table level, I wouldn't. It can become misleading since the formatting doesn't change the stored data at all, just the display.

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.

You can't have one query that returns mutliple records from one join and a single record from another join.

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.

Every table should have a primary key.
Date is a reserved word and shouldn't be used as an object name.
Date/Time fields are actually stored as decimal numbers. All you are doing with the format is changing the display (I know I'm repeating myself!). If you apply the formatting in the query, it does actually change the data in the results of the query (though not in the table itself).

I think you're trying to do everythng in a query when you should be doing this in a form or a report.

 

[wintermute]

Hello, thanks for taking an interest

Although you can apply formatting at table level, I wouldn't. It can become misleading since the formatting doesn't change the stored data at all, just the display.

You are quite correct, however in this case I am using this value in a small, simple application that will have a number of non computer savvy users. Hence I am trying to make it easy for them by formatting the date "Jan 2008" instead of "01/01/2008" when selecting a month.

You can't have one query that returns mutliple records from one join and a single record from another join.

True, my wording was wrong there apologies. I do not mind if the same Accuracy value shows up with the User's results throught each month, as long as I can get access to it through a query.

Every table should have a primary key.
Date is a reserved word and shouldn't be used as an object name.
Date/Time fields are actually stored as decimal numbers. All you are doing with the format is changing the display (I know I'm repeating myself!). If you apply the formatting in the query, it does actually change the data in the results of the query (though not in the table itself).

I have changed the Date as several people have pointed this out, doh The table Records is from an external source and I would like to leave it as it is to simplify things. However I have thought about normalizing it and could not really figure it out, maybe you can help.

It stores records for many Users and each User has records for many Dates, eg.

aalikhan 01/01/2008 56
aalikhan 04/01/2008 78
bambrose 02/01/2008 69
bambrose 03/02/2008 13
cdoodle ...
...

etc. Any suggestions on how to normalise that?

I think you're trying to do everythng in a query when you should be doing this in a form or a report.

The query runs from a form to generate values included in a report

Anyhow, since I posted with great help from another forum I have solved the problem like so:

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:

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

I think I just horribly misstated my problem instead of being clear about it. Thank you for your reply though