Reference multiple tables for a value

[dp724 via AccessMonster.com]

I have 2 SQLs that work individually to provide a single date in a drop down
combobox on my form (see below). How do I combine the 2 SQLs to setup a
single SQL statement to provide a date in the following scenario? If I select
a PSEID from the combobox and the PSEID is not found in the
TProgressExpenditures table; I then need the statement to use the TGoalDates
table to provide the date. The combined SQL should reference the
TProgressExpenditures table first for a PSEID and the date of the last
expenditure or else use that PSEID's last AwardDate in the TGoalDates.

SELECT TProgressExpenditures.PSEID, Format(Last(DateAdd("m",1,[month])),"mmm-
yyyy") AS Months
FROM TProgressExpenditures
GROUP BY TProgressExpenditures.PSEID
HAVING (((TProgressExpenditures.PSEID)=[Forms]![Update Progress &
Expenditures]![cboPSE]));

SELECT TGoalDates.PSEID, Format(Last(DateAdd("m",1,[AwardDate])),"mmm-yyyy")
AS Months
FROM TGoalDates
GROUP BY TGoalDates.PSEID
HAVING (((TGoalDates.PSEID)=[Forms]![Update Progress & Expenditures]![cboPSE])
);

Any help solving this problem is really appreciated.

 

[Jeff Boyce]

Take a look at Access HELP for particulars on the use of a UNION query.

The rough outline is that you'll drop the ";" in the first SQL statement,
add the word UNION (with proper spaces) and concatenate with the second SQL
statement.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[dp724 via AccessMonster.com]

Jeff,
I've tried the Union function; unfortunately, it provides 2 dates to chose
from; when the PSEID is listed in both tables. The user should see 1 date in
the combobox. The date should come from either the TProgressExpenditures
table, if the PSEID is listed or the TGoalDates table, if the expenditure is
being recorded for the first time. The idea behind the combobox is to provide
a date for which the expenditure is attached to; the user has no idea of the
date.

Hope to hear from you.

Take a look at Access HELP for particulars on the use of a UNION query.

The rough outline is that you'll drop the ";" in the first SQL statement,
add the word UNION (with proper spaces) and concatenate with the second SQL
statement.

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have 2 SQLs that work individually to provide a single date in a drop
down

[quoted text clipped - 26 lines]

Any help solving this problem is really appreciated.

 

[Jeff Boyce]

My mistake, I thought you wanted to be able to use both SQL statements.

It sounds like you want to change the row source of the combo box, depending
on some other condition.

You can do that in code, with something like:

Me!cboYourComboBox.RowSource = "YourQueryName"

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jeff,
I've tried the Union function; unfortunately, it provides 2 dates to chose
from; when the PSEID is listed in both tables. The user should see 1 date
in
the combobox. The date should come from either the TProgressExpenditures
table, if the PSEID is listed or the TGoalDates table, if the expenditure
is
being recorded for the first time. The idea behind the combobox is to
provide
a date for which the expenditure is attached to; the user has no idea of
the
date.

Hope to hear from you.

Take a look at Access HELP for particulars on the use of a UNION query.

The rough outline is that you'll drop the ";" in the first SQL statement,
add the word UNION (with proper spaces) and concatenate with the second
SQL
statement.

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have 2 SQLs that work individually to provide a single date in a drop
down

[quoted text clipped - 26 lines]

Any help solving this problem is really appreciated.

 

[dp724 via AccessMonster.com]

Jeff,
After further consideration about the UNION recommendation and what I thought
you were alluding to, I've set up one query gathering all dates and PSEID in
to one table, then created the query which the combobox uses to pull the date.


Query1:
SELECT TProgressExpenditures.PSEID, TProgressExpenditures.Month
FROM TProgressExpenditures UNION SELECT TGoalDates.PSEID, TGoalDates.
AwardDate
FROM TGoalDates
GROUP BY TGoalDates.PSEID, TGoalDates.AwardDate;

Query2:
SELECT Query1.PSEID, Format(Last(DateAdd("m",1,[Month])),"mmm-yyyy") AS Expr1
FROM Query1
GROUP BY Query1.PSEID
HAVING Query1.PSEID=[Forms]![Update Progress & Expenditures]![cboPSE];

Many thanks.

My mistake, I thought you wanted to be able to use both SQL statements.

It sounds like you want to change the row source of the combo box, depending
on some other condition.

You can do that in code, with something like:

Me!cboYourComboBox.RowSource = "YourQueryName"

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jeff,
I've tried the Union function; unfortunately, it provides 2 dates to chose

[quoted text clipped - 28 lines]