SQL through VB? Possible? 1

[jeffshex]

I got a tricky one.
For starters I need to create a temp table based on tblOrientation. Amongst this tblOrientation, there are 3 fields I'm looking to Join...SchedDate, SchedDate2, SchedDate3.
**I know it's silly to have it setup that way, but that's what's being used.**

I need to put them together so I can run a Dcount on it.
I'm pretty sure it needs to be one column

I have code on a form that runs the Dcount and validates to make sure that there are not more than 30 instances of one date...but I need to do this now with the 3 fields.

Is there a Join or something out there that I can do in the VB code or something.

I'm open to ideas.
Thanks.

 

[PHV]

Do a search in this forum for normalisation union query

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[jjlogan]

Jeff, I would need some clarification - as I inserted some questions in your thread: Your question:
For starters I need to create a temp table based on tblOrientation. Amongst this tblOrientation, there are 3 fields I'm looking to Join...SchedDate, SchedDate2, SchedDate3. I need to put them together so I can run a Dcount on it. [WHAT DO YOU NEED TO DO HERE - WHAT ARE YOU COUNTING?] I'm pretty sure it needs to be one column. I have code on a form that runs the Dcount and validates to make sure that there are not more than 30 instances of one date...but I need to do this now with the 3 fields.[DO YOU MEAN THAT IN YOUR TEMP TABLE THERE SHOULD NOT BE MORE THAN 30 UNIQUE VALUES OF THE COMBINED / CONCATENATED FIELD?]

Jeff L

 

[jeffshex]

No prob!

Let me get a little more in depth.
Here is the code I currently use. !!!Note that there is only one field named SchedDate!!

Private Sub SchedDate_BeforeUpdate(Cancel As Integer)
Dim DateChecking As Date
Dim TotalCount As Variant


' Check and make sure the date field isn't null
If IsNull(Me.SchedDate) Or Me.SchedDate = "" Then
Exit Sub
Else

DateChecking = Me.SchedDate.Value
TotalCount = DCount("[SchedDate]", "tblOrientation", "[SchedDate] = #" & DateChecking & "#")

    If TotalCount >= 30 Then
        MsgBox "The date you have selected has been filled to maximum capacity, please select a different date.", vbOKOnly, "Classroom Full"
        Cancel = True
    End If
End If
End Sub

This code successfully looks through the tblOrientation's [SchedDate] and will not let you enter more than 30 occurances of any particular date. *Classroom only has 30 seats*
Hopefully that part is clear. Now is the dilemma.

The person before me set up the tblOrientation to not only have the [SchedDate], but it also has the [SchedDate2], and [SchedDate3].

So now I have to look for the paricular date in ALL 3 Fields now. So the only way I'm guessing is to "merge or join" temporarily those 3 fields into ONE column or field.
That way, I can still use the DCount validation on that temp table.

Hopefully that clarifies. If not let me know and I'll answer right back!

Thanks!

 

[PHV]

You may try either this:
TotalCount = DCount("*", "tblOrientation", "SchedDate=#" & DateChecking & "# OR SchedDate2=#" & DateChecking & "# OR SchedDate3=#" & DateChecking & "#")
Or this:
TotalCount = DCount("*", "tblOrientation", "SchedDate=#" & DateChecking & "#") _
+ DCount("*", "tblOrientation", "SchedDate2=#" & DateChecking & "#") _
+ DCount("*", "tblOrientation", "SchedDate3=#" & DateChecking & "#")
Or this:
TotalCount = DCount("*", "tblOrientation", "#" & DateChecking & "# In (SchedDate,SchedDate2,SchedDate3)")

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[lespaul]

like PHV said, search for normalization query.

You'll end up with something like:

SELECT PK, "First Date", SchedDate FROM TableName
UNION
SELECT PK, "Second Date", SchedDate2 FROM TableName
UNION
SELECT PK, "Third Date", SchedDate3 FROM TableName


you only need to keep the "First Date" field if you need to know which was which.

HTH


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[jeffshex]

Alright...That second exmaple seems to be working great!
Thanks PHV