Lookup field

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I want my query to change every month. Basically I want to add in all
consistent fields for each month (name, number, total) and then have a new
field appear every month that is set to the latest month.

name, number, jan, Feb, Mar, Total

I want to set the field to recognize the last month in the series. Is there
a way to have the fields select "name", "number", "month (set to the field
before the total)", and "total"???
 
Basically I want to add in all
consistent fields for each month (name, number, total) and then have a
new field appear every month that is set to the latest month.
Click to expand...

Look up help on Cross-tab queries.


Tim F
 
Correct, the original creator was using a crosstab query, however I believe
there is a better way rather than having to input the paremeter every month.
I'm thinking that if the query recognizes that the field before the "Totals"
field is default as month value then I wouldn't have to enter it. I have a
paremeter set up in a table that is changed every month that comes up at the
beginning of the database, however it won't correspond with the lousy text
they use within the exporting database. I can certainly create a table and
cross-reference with those names, but am hoping you may have a better
solution as I would have to create a new table every time they made a change
in their application.
 
Correct, the original creator was using a crosstab query, however I
believe there is a better way rather than having to input the
paremeter every month.
Click to expand...

Remember that I cannot see your database so I can't really tell what is
going on. Some assumptions:

you are posting in the DAO/VBA group so you are prepared to do coding to
get what you want;

you have some kind of parameterised query that needs some kind of month
name passed in.

I guess you have tried the simple things like this:

set qdf = QueryDefs("ThisMonthsQuery")
qdf.Parameters("ThisMonth") = Format(Date(),"mmm")
set me.Recordset = qdf.OpenRecordset(dbOpenSnapshot)
I'm thinking that if the query recognizes that
the field before the "Totals" field is default as month value then I
wouldn't have to enter it.
Click to expand...

Harder... queries don't really recognise anything, least of all fields.
I'd really need to see the SQL to make anything of this (and even then...
I have a paremeter set up in a table that
is changed every month that comes up at the beginning of the database,
however it won't correspond with the lousy text they use within the
exporting database.
Click to expand...

Ah, the joys of proprietary Oracle database designers..!
I can certainly create a table and
cross-reference with those names, but am hoping you may have a better
solution
Click to expand...

As a last resort, you can always create your SQL query on the fly:

Public Sub CreateQuery()
Dim i As Integer
Dim jetSQL As String
Dim monthString As String

jetSQL = "SELECT " & vbNewLine

For i = -2 To 0 Step 1
monthString = Format( _
DateSerial(Year(Date), Month(Date) + i, Day(Date)), "mmm") _

jetSQL = jetSQL & " " & monthString & ", " & vbNewLine
Next i

jetSQL = jetSQL & " "

For i = -2 To 0 Step 1
monthString = Format( _
DateSerial(Year(Date), Month(Date) + i, Day(Date)), "mmm")

jetSQL = jetSQL & monthString & _
IIf(i < 0, " + ", " AS Total" & vbNewLine)
Next i

jetSQL = jetSQL & "FROM MyTable"

Debug.Assert vbYes = MsgBox(jetSQL, vbYesNo, "Is This Okay?")

End Sub


as I would have to create a new table
Click to expand...

** YEUCH!! **
every time they made a change in their application.
Click to expand...

Users shouldn't be making changes in an application -- that's a job for
the designers. If the source db admins keep changing their database app,
then you need new db admins!


Not sure how much help that is. Please post back with some real SQL if
you are able to.

B Wishes


Tim F
 
Back
Top