Complex Query

[Joe]

I've got a table with the following column headers well, year, Jan, feb,
mar, apr, may, jun, jul, aug, sept, oct, nov, dec.

I want to make a query so the well name will be in a column and the years
will be down the rows.

thanks,

 

[Michel Walsh]

SELECT well, DateSerial([year], 1, 1) As TheDate, Jan As TheData FROM
tableName
UNION ALL
SELECT well, DateSerial([year], 2, 1), Feb FROM tableName
UNION ALL
SELECT well, DateSerial([year], 3, 1), Mar FROM tableName
UNION ALL
SELECT well, DateSerial([year], 4, 1), Apr FROM tableName
UNION ALL
SELECT well, DateSerial([year], 5, 1), May FROM tableName
UNION ALL
SELECT well, DateSerial([year], 6, 1), Jun FROM tableName
UNION ALL
SELECT well, DateSerial([year], 7, 1), Jul FROM tableName
UNION ALL
SELECT well, DateSerial([year], 8, 1), Aug FROM tableName
UNION ALL
SELECT well, DateSerial([year], 9, 1), Sept FROM tableName
UNION ALL
SELECT well, DateSerial([year], 10, 1), Oct FROM tableName
UNION ALL
SELECT well, DateSerial([year], 11, 1), Nov FROM tableName
UNION ALL
SELECT well, DateSerial([year], 12, 1), Dec FROM tableName




is a possible solution.


Vanderghast, Access MVP

 

[DM]

you dont want a crosstab query do you?

 

[wrightlife11]

Hello, I have a problem with data type mismatch. Can someone assist me
please?

I have some basic date functions below. I keep getting a data type
mismatch. What can I do about it? I need to calcuate the number of days
between dates.
However when I use the Like 1 it will show up as all those with a 1.

I tried this query first:
SELECT MORDDate([SFStartDate]) AS MatOrdDate, Date() AS [Date],
CDbl(Dinq([SFStartDate],Date())) AS DaysPastMord
FROM tblMain
WHERE (((CDbl(Dinq([SFStartDate],Date())))<=1) AND ((tblMain.SFStartDate) Is
Not Null))
ORDER BY tblMain.SFStartDate;

Then I tried a query of a query like this:
SELECT
FROM qryX54DinQ
WHERE (((qryX54DinQ.DaysPastMord) <= 1));

and lastly I tried to use the Dinq function like this:
SELECT Dinq(TargetDate([SFStartDate]),Date()) AS Dinq
FROM qryX54DinQ
WHERE (((qryX54DinQ.DaysPastMord) Like -30) AND
((Dinq(TargetDate([SFStartDate]),Date()))<=1));

Example:
Public Function TargetDate(StartDate As Date) As Date


If StartDate < 0 Then 'Evaluate Argument
Exit Function
Else
TargetDate = StartDate - 7
End If

End Function

and

Public Function Dinq(StartDate As Date, EndDate As Date) As Double

If StartDate < 0 Then 'Evaluate Argument
Exit Function
Else
Dinq = EndDate - StartDate
End If


End Function

 

[John Spencer]

Why not just use the DateDiff function?

SELECT DateDiff("d",[SfStartDate],Date()) as DaysPastMord
FROM tblMain
WHERE SFStartDate is Not Null


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County