Query Help

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

Guest

I have the following tables: AETable (fields: AEID, LastName, FirstName),
Plan Data (fields: PlanID, PlanYear, Product1Plan$, Product2Plan$, AEID),
Actual Sold Data (fields: ActualID, AEID, SoldProd1$, SoldProd2$, ClientName,
PlanYear) and Committed Data (fields: CommID, AEID, FutureClientName,
EstSold$, PlanYear, TypeofComm {4 different types can be selected from
list}). All AEs will have a plan entered. However, not all AEs may have
data in the Actual or Committed tables. I would like to create a query that
based on the Plan Year selected [Enter Plan Year], I can see the Plan Data,
the Sold Data (even if zero), and the Committed data (even if zero) for each
AE. Similar to layout below:

LastName Plan Year Product1Plan$ Product2Plan$ Sold$ TypeofComm1
TypeofComm2 TypeofComm3 TypeofComm4
 
I have the following tables: AETable (fields: AEID, LastName, FirstName),
Plan Data (fields: PlanID, PlanYear, Product1Plan$, Product2Plan$, AEID),
Actual Sold Data (fields: ActualID, AEID, SoldProd1$, SoldProd2$, ClientName,
PlanYear) and Committed Data (fields: CommID, AEID, FutureClientName,
EstSold$, PlanYear, TypeofComm {4 different types can be selected from
list}). All AEs will have a plan entered. However, not all AEs may have
data in the Actual or Committed tables. I would like to create a query that
based on the Plan Year selected [Enter Plan Year], I can see the Plan Data,
the Sold Data (even if zero), and the Committed data (even if zero) for each
AE. Similar to layout below:

LastName Plan Year Product1Plan$ Product2Plan$ Sold$ TypeofComm1
TypeofComm2 TypeofComm3 TypeofComm4
Click to expand...

Create a Query with the three, joined by AEID. Select each Join line
and choose Option 2 (or 3) - "Show all data in Plan Data and matching
data in <the other table>".

Select what fields you want to see, and whaterver criteria you like.

The Left Join (the Option 2 suggestion above) is the key - it will
return all records from the "one" side table, whether or not there are
matches in the plan and committed tables.

John W. Vinson[MVP]
 
Karen-
open up the query builder and add all 4 of these tables. Drag joins between
these three pairs: AETable.AEID and planData.AEID, AETable.AEID and
SoldData.AEID, AETable.AEID and Committed.AEID. For the latter 2, right
click the join line, choose 'join properties' and select the option that
says "Select all records from table AETable and only those from Sold (or
Committed) where the fields match." Then choose which fields from any tables
you want to see. Then add your parameter in the criteria box under
PlanData.PlanYear. That should do it.
Post back if you have any problems.
-John
 
Thanks John, I built the query, but I am still having problems with the
dates. I would like to be able to enter one plan year to pull from all three
tables. If I put the [Enter Year] parameter in the tblPlan.PlanYear field,
it returns only those years, but also all years in the Committed and Actual
tables. I am unsure how to enter the year parameter to get the information
from all three tables.
John Welch said:
Karen-
open up the query builder and add all 4 of these tables. Drag joins between
these three pairs: AETable.AEID and planData.AEID, AETable.AEID and
SoldData.AEID, AETable.AEID and Committed.AEID. For the latter 2, right
click the join line, choose 'join properties' and select the option that
says "Select all records from table AETable and only those from Sold (or
Committed) where the fields match." Then choose which fields from any tables
you want to see. Then add your parameter in the criteria box under
PlanData.PlanYear. That should do it.
Post back if you have any problems.
-John

Karen said:
I have the following tables: AETable (fields: AEID, LastName, FirstName),
Plan Data (fields: PlanID, PlanYear, Product1Plan$, Product2Plan$, AEID),
Actual Sold Data (fields: ActualID, AEID, SoldProd1$, SoldProd2$,
ClientName,
PlanYear) and Committed Data (fields: CommID, AEID, FutureClientName,
EstSold$, PlanYear, TypeofComm {4 different types can be selected from
list}). All AEs will have a plan entered. However, not all AEs may have
data in the Actual or Committed tables. I would like to create a query
that
based on the Plan Year selected [Enter Plan Year], I can see the Plan
Data,
the Sold Data (even if zero), and the Committed data (even if zero) for
each
AE. Similar to layout below:

LastName Plan Year Product1Plan$ Product2Plan$ Sold$
TypeofComm1
TypeofComm2 TypeofComm3 TypeofComm4
Click to expand...
Click to expand...
 
I forgot to mention that I need to be able to see those AEs that have no data
in the committed table as well as those with the plan year that is selected
for the Plan and Actual tables.

Karen said:
Thanks John, I built the query, but I am still having problems with the
dates. I would like to be able to enter one plan year to pull from all three
tables. If I put the [Enter Year] parameter in the tblPlan.PlanYear field,
it returns only those years, but also all years in the Committed and Actual
tables. I am unsure how to enter the year parameter to get the information
from all three tables.
John Welch said:
Karen-
open up the query builder and add all 4 of these tables. Drag joins between
these three pairs: AETable.AEID and planData.AEID, AETable.AEID and
SoldData.AEID, AETable.AEID and Committed.AEID. For the latter 2, right
click the join line, choose 'join properties' and select the option that
says "Select all records from table AETable and only those from Sold (or
Committed) where the fields match." Then choose which fields from any tables
you want to see. Then add your parameter in the criteria box under
PlanData.PlanYear. That should do it.
Post back if you have any problems.
-John

Karen said:
I have the following tables: AETable (fields: AEID, LastName, FirstName),
Plan Data (fields: PlanID, PlanYear, Product1Plan$, Product2Plan$, AEID),
Actual Sold Data (fields: ActualID, AEID, SoldProd1$, SoldProd2$,
ClientName,
PlanYear) and Committed Data (fields: CommID, AEID, FutureClientName,
EstSold$, PlanYear, TypeofComm {4 different types can be selected from
list}). All AEs will have a plan entered. However, not all AEs may have
data in the Actual or Committed tables. I would like to create a query
that
based on the Plan Year selected [Enter Plan Year], I can see the Plan
Data,
the Sold Data (even if zero), and the Committed data (even if zero) for
each
AE. Similar to layout below:

LastName Plan Year Product1Plan$ Product2Plan$ Sold$
TypeofComm1
TypeofComm2 TypeofComm3 TypeofComm4
Click to expand...
Click to expand...
Click to expand...
 
So you want to exclude records from Committed and Actual that don't have the
same year as that you selected as the criterion (but still show AE's with
null committed and actual). Ok. In the query builder drag links from
Committed.PlanYear to AE.PlanYear and from Actual.PlanYear to AE.planYear.
Make them left joins just like the other 2 (arrows pointing to Committed and
Actual tables). That should do what you want.
-john
Thanks John, I built the query, but I am still having problems with the
dates. I would like to be able to enter one plan year to pull from all
three
tables. If I put the [Enter Year] parameter in the tblPlan.PlanYear
field,
it returns only those years, but also all years in the Committed and
Actual
tables. I am unsure how to enter the year parameter to get the
information
from all three tables.
John Welch said:
Karen-
open up the query builder and add all 4 of these tables. Drag joins
between
these three pairs: AETable.AEID and planData.AEID, AETable.AEID and
SoldData.AEID, AETable.AEID and Committed.AEID. For the latter 2, right
click the join line, choose 'join properties' and select the option
that
says "Select all records from table AETable and only those from Sold
(or
Committed) where the fields match." Then choose which fields from any
tables
you want to see. Then add your parameter in the criteria box under
PlanData.PlanYear. That should do it.
Post back if you have any problems.
-John

I have the following tables: AETable (fields: AEID, LastName,
FirstName),
Plan Data (fields: PlanID, PlanYear, Product1Plan$, Product2Plan$,
AEID),
Actual Sold Data (fields: ActualID, AEID, SoldProd1$, SoldProd2$,
ClientName,
PlanYear) and Committed Data (fields: CommID, AEID, FutureClientName,
EstSold$, PlanYear, TypeofComm {4 different types can be selected
from
list}). All AEs will have a plan entered. However, not all AEs may
have
data in the Actual or Committed tables. I would like to create a
query
that
based on the Plan Year selected [Enter Plan Year], I can see the Plan
Data,
the Sold Data (even if zero), and the Committed data (even if zero)
for
each
AE. Similar to layout below:

LastName Plan Year Product1Plan$ Product2Plan$ Sold$
TypeofComm1
TypeofComm2 TypeofComm3 TypeofComm4
Click to expand...
Click to expand...
Click to expand...
 
Back
Top