All records are not showing on report

[Ricoy-Chicago]

I have created a report with the fields: [LeadCode], [NoOfLeads] (recorded by
user, relates to the Lead Code) and [LeadCost]. This report is based on a
query where [LeadCode] is a field in the [LeadInfo] table, [NoOfLeads] is a
field in the [CallsLogInfo] table and [LeadCost] is a field in the table
[CostsInfo]. Thsi report is based on certain dates as required by Admin, no
problem here, everything runs ok, the problem is:

If a given Lead Code is not used in the dates included in the report, that
Lead Code does not show in the report because there are no records related to
it in the [CallLogInfo] table; however, this LeadCode has to show so its cost
can be added to the total Costs fro the given period of time.

Any help will be greatly appreciated. Thank you.

 

[Jerry Whittle]

The problem is with the query. Open it in design view. Double-click on the
line between the two tables until a dialog box shows. Select the second
option. If that doesn't work, try the third option.

 

[Ricoy-Chicago]

Jerry,
Itried both options and I get the same rror message "The SQL statemnet could
not be executed because it contains ambiguous joins. To force one of these
joins to be performed first, cretae a separate qry that performs the first
join and then include that query in oyur SQL statement".
How do I know which join is to be performed first?

The problem is with the query. Open it in design view. Double-click on the
line between the two tables until a dialog box shows. Select the second
option. If that doesn't work, try the third option.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I have created a report with the fields: [LeadCode], [NoOfLeads] (recorded by
user, relates to the Lead Code) and [LeadCost]. This report is based on a
query where [LeadCode] is a field in the [LeadInfo] table, [NoOfLeads] is a
field in the [CallsLogInfo] table and [LeadCost] is a field in the table
[CostsInfo]. Thsi report is based on certain dates as required by Admin, no
problem here, everything runs ok, the problem is:

If a given Lead Code is not used in the dates included in the report, that
Lead Code does not show in the report because there are no records related to
it in the [CallLogInfo] table; however, this LeadCode has to show so its cost
can be added to the total Costs fro the given period of time.

Any help will be greatly appreciated. Thank you.

 

[Jerry Whittle]

Dang! I thought that this was an easy question to answer. ;-)

Show us the SQL. Open the query in design view. Next go to View, SQL View
and copy and past it here. Information on primary keys and relationships
would be a nice touch too.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Jerry,
Itried both options and I get the same rror message "The SQL statemnet could
not be executed because it contains ambiguous joins. To force one of these
joins to be performed first, cretae a separate qry that performs the first
join and then include that query in oyur SQL statement".
How do I know which join is to be performed first?

The problem is with the query. Open it in design view. Double-click on the
line between the two tables until a dialog box shows. Select the second
option. If that doesn't work, try the third option.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I have created a report with the fields: [LeadCode], [NoOfLeads] (recorded by
user, relates to the Lead Code) and [LeadCost]. This report is based on a
query where [LeadCode] is a field in the [LeadInfo] table, [NoOfLeads] is a
field in the [CallsLogInfo] table and [LeadCost] is a field in the table
[CostsInfo]. Thsi report is based on certain dates as required by Admin, no
problem here, everything runs ok, the problem is:

If a given Lead Code is not used in the dates included in the report, that
Lead Code does not show in the report because there are no records related to
it in the [CallLogInfo] table; however, this LeadCode has to show so its cost
can be added to the total Costs fro the given period of time.

Any help will be greatly appreciated. Thank you.

 

[Ricoy-Chicago]

SQL:
SELECT [Calls Data].[log date], [Calls Data].ID, [Calls Data].[Lead Source
Description], [Calls Data].[MissingLeadCard?], [Cost per Lead].Cost, [Cost
per Lead].WeekNo, [Calls Data].[Extension Code], [Lead Source Data].[Lead
Source Code]
FROM ([Cost per Lead] INNER JOIN ([Lead Source Data] INNER JOIN [Calls Data]
ON [Lead Source Data].[Lead Source Description] = [Calls Data].[Lead Source
Description]) ON [Cost per Lead].[Lead Source Code] = [Lead Source
Data].[Lead Source Code]) INNER JOIN [Log Date Data] ON ([Calls Data].[log
date] = [Log Date Data].[Log Date]) AND ([Cost per Lead].WeekNo = [Log Date
Data].WeekNo)
WHERE ((([Calls Data].[log date]) Between #1/1/2008# And #1/31/2008#));

PK in [Calls Data] table: ID
PK in [Lead Source Data] table: Lead Description
PK in [Log Date Data] Table: Log Date
PK in [Cost Per Lead] table: ID

The PK in Calls data is ID because you may have caller with the same last name
The PK in [Cost per Lead] is ID befause you have the same lead having
different costs in different weeks.

The "between and" will be actually entered via a form (no problem here) I
entered the dates for the month of January just to correlate with Admin.
figures. They agree except for the problem mentioned above.

Thank you for all your help

Dang! I thought that this was an easy question to answer. ;-)

Show us the SQL. Open the query in design view. Next go to View, SQL View
and copy and past it here. Information on primary keys and relationships
would be a nice touch too.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Jerry,
Itried both options and I get the same rror message "The SQL statemnet could
not be executed because it contains ambiguous joins. To force one of these
joins to be performed first, cretae a separate qry that performs the first
join and then include that query in oyur SQL statement".
How do I know which join is to be performed first?

The problem is with the query. Open it in design view. Double-click on the
line between the two tables until a dialog box shows. Select the second
option. If that doesn't work, try the third option.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


:

I have created a report with the fields: [LeadCode], [NoOfLeads] (recorded by
user, relates to the Lead Code) and [LeadCost]. This report is based on a
query where [LeadCode] is a field in the [LeadInfo] table, [NoOfLeads] is a
field in the [CallsLogInfo] table and [LeadCost] is a field in the table
[CostsInfo]. Thsi report is based on certain dates as required by Admin, no
problem here, everything runs ok, the problem is:

If a given Lead Code is not used in the dates included in the report, that
Lead Code does not show in the report because there are no records related to
it in the [CallLogInfo] table; however, this LeadCode has to show so its cost
can be added to the total Costs fro the given period of time.

Any help will be greatly appreciated. Thank you.

 

[Jerry Whittle]

Hi,

It looks like you are joining the following tables by these fields:

[Cost per Lead].[Lead Source Code] = [Lead Source Data].[Lead Source Code])
[Cost per Lead].WeekNo = [Log Date Data].WeekNo)

Unfortunately neither side is a primary key field. That is probably the root
cause of the "ambiguous joins" error as it can return a Cartisian product.
Sorry to say that from this side of the monitor, I really can't make even a
good guess on how to fix the problem.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

SQL:
SELECT [Calls Data].[log date], [Calls Data].ID, [Calls Data].[Lead Source
Description], [Calls Data].[MissingLeadCard?], [Cost per Lead].Cost, [Cost
per Lead].WeekNo, [Calls Data].[Extension Code], [Lead Source Data].[Lead
Source Code]
FROM ([Cost per Lead] INNER JOIN ([Lead Source Data] INNER JOIN [Calls Data]
ON [Lead Source Data].[Lead Source Description] = [Calls Data].[Lead Source
Description]) ON [Cost per Lead].[Lead Source Code] = [Lead Source
Data].[Lead Source Code]) INNER JOIN [Log Date Data] ON ([Calls Data].[log
date] = [Log Date Data].[Log Date]) AND ([Cost per Lead].WeekNo = [Log Date
Data].WeekNo)
WHERE ((([Calls Data].[log date]) Between #1/1/2008# And #1/31/2008#));

PK in [Calls Data] table: ID
PK in [Lead Source Data] table: Lead Description
PK in [Log Date Data] Table: Log Date
PK in [Cost Per Lead] table: ID

The PK in Calls data is ID because you may have caller with the same last name
The PK in [Cost per Lead] is ID befause you have the same lead having
different costs in different weeks.

The "between and" will be actually entered via a form (no problem here) I
entered the dates for the month of January just to correlate with Admin.
figures. They agree except for the problem mentioned above.

Thank you for all your help

Dang! I thought that this was an easy question to answer. ;-)

Show us the SQL. Open the query in design view. Next go to View, SQL View
and copy and past it here. Information on primary keys and relationships
would be a nice touch too.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Jerry,
Itried both options and I get the same rror message "The SQL statemnet could
not be executed because it contains ambiguous joins. To force one of these
joins to be performed first, cretae a separate qry that performs the first
join and then include that query in oyur SQL statement".
How do I know which join is to be performed first?


:

The problem is with the query. Open it in design view. Double-click on the
line between the two tables until a dialog box shows. Select the second
option. If that doesn't work, try the third option.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


:

I have created a report with the fields: [LeadCode], [NoOfLeads] (recorded by
user, relates to the Lead Code) and [LeadCost]. This report is based on a
query where [LeadCode] is a field in the [LeadInfo] table, [NoOfLeads] is a
field in the [CallsLogInfo] table and [LeadCost] is a field in the table
[CostsInfo]. Thsi report is based on certain dates as required by Admin, no
problem here, everything runs ok, the problem is:

If a given Lead Code is not used in the dates included in the report, that
Lead Code does not show in the report because there are no records related to
it in the [CallLogInfo] table; however, this LeadCode has to show so its cost
can be added to the total Costs fro the given period of time.

Any help will be greatly appreciated. Thank you.

 

[Ricoy-Chicago]

You are correct the neither of the joining fields is a PK! Thanks didn't
see that coming.

Hi,

It looks like you are joining the following tables by these fields:

[Cost per Lead].[Lead Source Code] = [Lead Source Data].[Lead Source Code])
[Cost per Lead].WeekNo = [Log Date Data].WeekNo)

Unfortunately neither side is a primary key field. That is probably the root
cause of the "ambiguous joins" error as it can return a Cartisian product.
Sorry to say that from this side of the monitor, I really can't make even a
good guess on how to fix the problem.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

SQL:
SELECT [Calls Data].[log date], [Calls Data].ID, [Calls Data].[Lead Source
Description], [Calls Data].[MissingLeadCard?], [Cost per Lead].Cost, [Cost
per Lead].WeekNo, [Calls Data].[Extension Code], [Lead Source Data].[Lead
Source Code]
FROM ([Cost per Lead] INNER JOIN ([Lead Source Data] INNER JOIN [Calls Data]
ON [Lead Source Data].[Lead Source Description] = [Calls Data].[Lead Source
Description]) ON [Cost per Lead].[Lead Source Code] = [Lead Source
Data].[Lead Source Code]) INNER JOIN [Log Date Data] ON ([Calls Data].[log
date] = [Log Date Data].[Log Date]) AND ([Cost per Lead].WeekNo = [Log Date
Data].WeekNo)
WHERE ((([Calls Data].[log date]) Between #1/1/2008# And #1/31/2008#));

PK in [Calls Data] table: ID
PK in [Lead Source Data] table: Lead Description
PK in [Log Date Data] Table: Log Date
PK in [Cost Per Lead] table: ID

The PK in Calls data is ID because you may have caller with the same last name
The PK in [Cost per Lead] is ID befause you have the same lead having
different costs in different weeks.

The "between and" will be actually entered via a form (no problem here) I
entered the dates for the month of January just to correlate with Admin.
figures. They agree except for the problem mentioned above.

Thank you for all your help

Dang! I thought that this was an easy question to answer. ;-)

Show us the SQL. Open the query in design view. Next go to View, SQL View
and copy and past it here. Information on primary keys and relationships
would be a nice touch too.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

:

Jerry,
Itried both options and I get the same rror message "The SQL statemnet could
not be executed because it contains ambiguous joins. To force one of these
joins to be performed first, cretae a separate qry that performs the first
join and then include that query in oyur SQL statement".
How do I know which join is to be performed first?


:

The problem is with the query. Open it in design view. Double-click on the
line between the two tables until a dialog box shows. Select the second
option. If that doesn't work, try the third option.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


:

I have created a report with the fields: [LeadCode], [NoOfLeads] (recorded by
user, relates to the Lead Code) and [LeadCost]. This report is based on a
query where [LeadCode] is a field in the [LeadInfo] table, [NoOfLeads] is a
field in the [CallsLogInfo] table and [LeadCost] is a field in the table
[CostsInfo]. Thsi report is based on certain dates as required by Admin, no
problem here, everything runs ok, the problem is:

If a given Lead Code is not used in the dates included in the report, that
Lead Code does not show in the report because there are no records related to
it in the [CallLogInfo] table; however, this LeadCode has to show so its cost
can be added to the total Costs fro the given period of time.

Any help will be greatly appreciated. Thank you.