Programatically Change Joins

[Sandy]

Is there any way to change field joins in an sql statement or query based on
some criteria?

For example:

If the current month is January then I want to join TableA.Field1 to
TableB.January However, if the month is February then I want to join
TableA.Field1 to TableB.February, without manually changing the link to
TableB.

Coincidentally, I would always be referencing the previous month Date()-31
in the database which would = the field name of TableB that I want to have
TableA joined to.

Your help is much appreciated on how to accomplish the above.

Thanks!

 

[Marshall Barton]

Is there any way to change field joins in an sql statement or query based on
some criteria?

For example:

If the current month is January then I want to join TableA.Field1 to
TableB.January However, if the month is February then I want to join
TableA.Field1 to TableB.February, without manually changing the link to
TableB.

Coincidentally, I would always be referencing the previous month Date()-31
in the database which would = the field name of TableB that I want to have
TableA joined to.

Your help is much appreciated on how to accomplish the above.

Sandy, you are not going to get much help with that
spreadsheet-like arrangement you're struggling with. It's
just plain too far from the standard relational database
approach for any one to be helpful. With your existing
design, you are going to be continually running into these
complex road blocks.

I suggest that you think about your data organization and
then ask for help in the tabledesign newsgroup to see if you
can put together a more normalized table structure.

 

[Sandy]

Mr. Barton,

I appreciate your effort to be helpful. However my tables are normalized and
I do not need assistance with structuring my database design. I simply asked
if there was a way to use SQL in VBA and based on some criteria have the
joins change dynamically.

This must not be possible otherwise you would have assisted me more kindly.

Regards,

Ms. Skaar

 

[Douglas J. Steele]

If you've got fields named "January", "Febuary", etc., then your data is NOT
normalized. You've got data embedded in the name of the field, for one
thing.

Yes, you can change the SQL in a QueryDef object without any problem. You
need to have a reference set to DAO, and then use something like:

Dim dbCurr As DAO.Database
Dim qdfCurr As DAO.QueryDef
Dim strSQL As String

Set dbCurr = CurrentDb()
Set qdfCurr = dbCurr.QueryDefs("MyStoredQuery")
strSQL = qdfCurr.SQL
strSQL = Replace(strSQL, ".January", ".February")
qdfCurr.SQL= strSQL

Set qdfCurr = Nothing
Set dbCurr = Nothing

I'll echo Marsh's comment, though, that you're just asking for future
problems!

 

[Marshall Barton]

I'm being as kind as I can about your need to restructure
your tables. Any table that has repeating fields such as
your Jan, Feb, etc. is just going to cause all kinds of
headaches as your project moves forward. This particular
question is just the tip of the iceburg of troubles you will
eventually have to deal with. You may not fully appreciate
that at this point so I'm trying to provide a voice of
experience in warning you of the treacherous path you're
heading down.

You're welcome to gather other opinions, but I suspect that
there are very few people that have used that kind of
structure so there is almost no one around to help you deal
with its issues.

Since I have given the best answer I know to your question,
I'll bow out ot this thread and let others contribute their
ideas.

 

[Sandy]

Mr. Steele and Mr. Marsh,

Thank you both for your opinion and assistance.

Regards,

Ms. Skaar