Need help with a query error

[jkendrick75]

when i run the query as a query, i get the correct answers. however when i
try to open the report that runs off of the query, i am getting an error
"This expression is typed incorrectly, or it is too complex to be evaluated.
for example, a numeric expression may contain too many complicated elements.
Try simplifying the expression by assigning parts of the expression to
variables."

this report used to work, before i deleted a table, and added a different
table to the database. both tables had the same data, just one had several
other fields that were not necessary. the one that i'm using now just has
two fields. Using the one with the two fields was much easier to normalize,
as it had some duplicate values. When i ran the report, instead of showing
19 records like it should have, it was showing 28 records due to all of the
duplication of data in the three tables that the query works off of.

now some of the spaces, when ran as a query, are blank. i have set it up in
the report to automatically fill blanks with 1's, so as to not get other
display areas to show "DIV0#" or something like that.

The sql for the query is below:
---------------------------------------------------------------------------
SELECT tblDefectCount.Operator, tblDefectCount.Comments,
tblDefectCount.Code, tblDefectCount.PressNumber, tblDefectCount.MoldNumber,
tblDefectCount.ActualOps, tblDefectCount.UpTime, tblDefectCount.CycleTime,
tblDefectCount.ActualCavities, DBA_part_machine1.crew_size,
DBA_part_machine1.cycle_time, DBA_part_machine1.parts_per_cycle,
tblDefectCount.TotalPartsRan, tblDefectCount.TotalDefects,
tblDefectCount.DownTime
FROM (tblDefectCount LEFT JOIN DBA_part_customer1 ON
tblDefectCount.PartNumber = DBA_part_customer1.customer_part) LEFT JOIN
DBA_part_machine1 ON DBA_part_customer1.part = DBA_part_machine1.part
WHERE
(((tblDefectCount.WorkDate)=[forms]![frmDailyProductionReport].[txtDate]) AND
((tblDefectCount.Shift)=[forms]![frmDailyProductionReport].[txtShift]) AND
((tblDefectCount.Plant)=[forms]![frmdailyproductionReport].[txtPlant]))
GROUP BY tblDefectCount.Operator, tblDefectCount.Comments,
tblDefectCount.Code, tblDefectCount.PressNumber, tblDefectCount.MoldNumber,
tblDefectCount.ActualOps, tblDefectCount.UpTime, tblDefectCount.CycleTime,
tblDefectCount.ActualCavities, DBA_part_machine1.crew_size,
DBA_part_machine1.cycle_time, DBA_part_machine1.parts_per_cycle,
tblDefectCount.TotalPartsRan, tblDefectCount.TotalDefects,
tblDefectCount.DownTime;

 

[AccessAddict]

Hi jkendrick75,

It sounds to me like you might have a type of relationship problem as well
as a query problem...I could be wrong ...

I say this because of the additional records. Do you have a table
associated with this query that has 9 records in it? Try changing your
relationship type between the tables in your query and see if it helps
(keeping the one-to-many relationship in mind). The "complex query" problem
can usually be solved by creating a new query and using the query that you
are in to connect to this other table that you have probably added...just a
quick guess...but its worth a try.

Good luck.

when i run the query as a query, i get the correct answers. however when i
try to open the report that runs off of the query, i am getting an error
"This expression is typed incorrectly, or it is too complex to be evaluated.
for example, a numeric expression may contain too many complicated elements.
Try simplifying the expression by assigning parts of the expression to
variables."

this report used to work, before i deleted a table, and added a different
table to the database. both tables had the same data, just one had several
other fields that were not necessary. the one that i'm using now just has
two fields. Using the one with the two fields was much easier to normalize,
as it had some duplicate values. When i ran the report, instead of showing
19 records like it should have, it was showing 28 records due to all of the
duplication of data in the three tables that the query works off of.

now some of the spaces, when ran as a query, are blank. i have set it up in
the report to automatically fill blanks with 1's, so as to not get other
display areas to show "DIV0#" or something like that.

The sql for the query is below:
---------------------------------------------------------------------------
SELECT tblDefectCount.Operator, tblDefectCount.Comments,
tblDefectCount.Code, tblDefectCount.PressNumber, tblDefectCount.MoldNumber,
tblDefectCount.ActualOps, tblDefectCount.UpTime, tblDefectCount.CycleTime,
tblDefectCount.ActualCavities, DBA_part_machine1.crew_size,
DBA_part_machine1.cycle_time, DBA_part_machine1.parts_per_cycle,
tblDefectCount.TotalPartsRan, tblDefectCount.TotalDefects,
tblDefectCount.DownTime
FROM (tblDefectCount LEFT JOIN DBA_part_customer1 ON
tblDefectCount.PartNumber = DBA_part_customer1.customer_part) LEFT JOIN
DBA_part_machine1 ON DBA_part_customer1.part = DBA_part_machine1.part
WHERE
(((tblDefectCount.WorkDate)=[forms]![frmDailyProductionReport].[txtDate]) AND
((tblDefectCount.Shift)=[forms]![frmDailyProductionReport].[txtShift]) AND
((tblDefectCount.Plant)=[forms]![frmdailyproductionReport].[txtPlant]))
GROUP BY tblDefectCount.Operator, tblDefectCount.Comments,
tblDefectCount.Code, tblDefectCount.PressNumber, tblDefectCount.MoldNumber,
tblDefectCount.ActualOps, tblDefectCount.UpTime, tblDefectCount.CycleTime,
tblDefectCount.ActualCavities, DBA_part_machine1.crew_size,
DBA_part_machine1.cycle_time, DBA_part_machine1.parts_per_cycle,
tblDefectCount.TotalPartsRan, tblDefectCount.TotalDefects,
tblDefectCount.DownTime;

 

[jkendrick75]

thanks for the reply.
i had tried changing the relationships in the query itself between each of
the tables, using each of the different types. Each time i still got 28
records, instead of the 19, except for one setup where i received an error of
"ambiguous outer joins". And other than making the joins in the query with
the new table, it is not referenced in the query. the table is just used to
convert an external part number into an internal part number to get the
'parts_per_cycle', 'cycle_time', and 'crew_size' from the "DBA_part_machine1"
table. Which is what the previous table did, just the previous table had a
lot more in the way of fields, as well as duplicated data. So the query is
the same, and nothing on the report is referencing anything from the new
table, just as it wasn't referencing anything from the previous table. as
far as using another query to connect to the new table, i'm not sure what
your meaning or how to accomplish it. thanks again, and any other ideas/help
would be welcome.

Hi jkendrick75,

It sounds to me like you might have a type of relationship problem as well
as a query problem...I could be wrong ...

I say this because of the additional records. Do you have a table
associated with this query that has 9 records in it? Try changing your
relationship type between the tables in your query and see if it helps
(keeping the one-to-many relationship in mind). The "complex query" problem
can usually be solved by creating a new query and using the query that you
are in to connect to this other table that you have probably added...just a
quick guess...but its worth a try.

Good luck.

when i run the query as a query, i get the correct answers. however when i
try to open the report that runs off of the query, i am getting an error
"This expression is typed incorrectly, or it is too complex to be evaluated.
for example, a numeric expression may contain too many complicated elements.
Try simplifying the expression by assigning parts of the expression to
variables."

this report used to work, before i deleted a table, and added a different
table to the database. both tables had the same data, just one had several
other fields that were not necessary. the one that i'm using now just has
two fields. Using the one with the two fields was much easier to normalize,
as it had some duplicate values. When i ran the report, instead of showing
19 records like it should have, it was showing 28 records due to all of the
duplication of data in the three tables that the query works off of.

now some of the spaces, when ran as a query, are blank. i have set it up in
the report to automatically fill blanks with 1's, so as to not get other
display areas to show "DIV0#" or something like that.

The sql for the query is below:
---------------------------------------------------------------------------
SELECT tblDefectCount.Operator, tblDefectCount.Comments,
tblDefectCount.Code, tblDefectCount.PressNumber, tblDefectCount.MoldNumber,
tblDefectCount.ActualOps, tblDefectCount.UpTime, tblDefectCount.CycleTime,
tblDefectCount.ActualCavities, DBA_part_machine1.crew_size,
DBA_part_machine1.cycle_time, DBA_part_machine1.parts_per_cycle,
tblDefectCount.TotalPartsRan, tblDefectCount.TotalDefects,
tblDefectCount.DownTime
FROM (tblDefectCount LEFT JOIN DBA_part_customer1 ON
tblDefectCount.PartNumber = DBA_part_customer1.customer_part) LEFT JOIN
DBA_part_machine1 ON DBA_part_customer1.part = DBA_part_machine1.part
WHERE
(((tblDefectCount.WorkDate)=[forms]![frmDailyProductionReport].[txtDate]) AND
((tblDefectCount.Shift)=[forms]![frmDailyProductionReport].[txtShift]) AND
((tblDefectCount.Plant)=[forms]![frmdailyproductionReport].[txtPlant]))
GROUP BY tblDefectCount.Operator, tblDefectCount.Comments,
tblDefectCount.Code, tblDefectCount.PressNumber, tblDefectCount.MoldNumber,
tblDefectCount.ActualOps, tblDefectCount.UpTime, tblDefectCount.CycleTime,
tblDefectCount.ActualCavities, DBA_part_machine1.crew_size,
DBA_part_machine1.cycle_time, DBA_part_machine1.parts_per_cycle,
tblDefectCount.TotalPartsRan, tblDefectCount.TotalDefects,
tblDefectCount.DownTime;

 

[AccessAddict]

tblDefectCount
DBA_part_machine1
tblDefectCount
DBA_part_customer1

I count four tables from the code you provided.

Whenever I get the ambiguous outer joins (and it don't make sense to me) I
remove the join and table/query (that I am trying to add and that it is
joining) and create a new query with the old query (that worked) in it, and
add the table/query that it wouldn't let me join earlier and it will now let
me join it the way I want.

Now that I have re-read your problem I wonder if it is as simple as an
expression on your report being too complicated...somehow I missed this the
first time. Check the underlying expressions on the report to see if any of
them are referencing some field on the table you say is not being used. If
it is not, maybe rethink the expressions that are being used on the report.
You say your query works but not the report--correct? Then I would say the
problem is in an expression in the report not the query.

Blanks in your query lead me to think that one of the common fields that are
used in the query is referencing the table that don't have records for that
field...do some records in that field have data?

Just crazy thoughts here...maybe?

thanks for the reply.
i had tried changing the relationships in the query itself between each of
the tables, using each of the different types. Each time i still got 28
records, instead of the 19, except for one setup where i received an error of
"ambiguous outer joins". And other than making the joins in the query with
the new table, it is not referenced in the query. the table is just used to
convert an external part number into an internal part number to get the
'parts_per_cycle', 'cycle_time', and 'crew_size' from the "DBA_part_machine1"
table. Which is what the previous table did, just the previous table had a
lot more in the way of fields, as well as duplicated data. So the query is
the same, and nothing on the report is referencing anything from the new
table, just as it wasn't referencing anything from the previous table. as
far as using another query to connect to the new table, i'm not sure what
your meaning or how to accomplish it. thanks again, and any other ideas/help
would be welcome.

Hi jkendrick75,

It sounds to me like you might have a type of relationship problem as well
as a query problem...I could be wrong ...

I say this because of the additional records. Do you have a table
associated with this query that has 9 records in it? Try changing your
relationship type between the tables in your query and see if it helps
(keeping the one-to-many relationship in mind). The "complex query" problem
can usually be solved by creating a new query and using the query that you
are in to connect to this other table that you have probably added...just a
quick guess...but its worth a try.

Good luck.

when i run the query as a query, i get the correct answers. however when i
try to open the report that runs off of the query, i am getting an error
"This expression is typed incorrectly, or it is too complex to be evaluated.
for example, a numeric expression may contain too many complicated elements.
Try simplifying the expression by assigning parts of the expression to
variables."

this report used to work, before i deleted a table, and added a different
table to the database. both tables had the same data, just one had several
other fields that were not necessary. the one that i'm using now just has
two fields. Using the one with the two fields was much easier to normalize,
as it had some duplicate values. When i ran the report, instead of showing
19 records like it should have, it was showing 28 records due to all of the
duplication of data in the three tables that the query works off of.

now some of the spaces, when ran as a query, are blank. i have set it up in
the report to automatically fill blanks with 1's, so as to not get other
display areas to show "DIV0#" or something like that.

The sql for the query is below:
---------------------------------------------------------------------------
SELECT tblDefectCount.Operator, tblDefectCount.Comments,
tblDefectCount.Code, tblDefectCount.PressNumber, tblDefectCount.MoldNumber,
tblDefectCount.ActualOps, tblDefectCount.UpTime, tblDefectCount.CycleTime,
tblDefectCount.ActualCavities, DBA_part_machine1.crew_size,
DBA_part_machine1.cycle_time, DBA_part_machine1.parts_per_cycle,
tblDefectCount.TotalPartsRan, tblDefectCount.TotalDefects,
tblDefectCount.DownTime
FROM (tblDefectCount LEFT JOIN DBA_part_customer1 ON
tblDefectCount.PartNumber = DBA_part_customer1.customer_part) LEFT JOIN
DBA_part_machine1 ON DBA_part_customer1.part = DBA_part_machine1.part
WHERE
(((tblDefectCount.WorkDate)=[forms]![frmDailyProductionReport].[txtDate]) AND
((tblDefectCount.Shift)=[forms]![frmDailyProductionReport].[txtShift]) AND
((tblDefectCount.Plant)=[forms]![frmdailyproductionReport].[txtPlant]))
GROUP BY tblDefectCount.Operator, tblDefectCount.Comments,
tblDefectCount.Code, tblDefectCount.PressNumber, tblDefectCount.MoldNumber,
tblDefectCount.ActualOps, tblDefectCount.UpTime, tblDefectCount.CycleTime,
tblDefectCount.ActualCavities, DBA_part_machine1.crew_size,
DBA_part_machine1.cycle_time, DBA_part_machine1.parts_per_cycle,
tblDefectCount.TotalPartsRan, tblDefectCount.TotalDefects,
tblDefectCount.DownTime;

 

[jkendrick75]

i was able to fix it. it was an expression. i copied the report to another
name, then removed all text boxes that had any kind of computations taking
place, then added text boxes from the old one to the new one one at a time.
i found out that i was trying to do a sum() to a set of numbers that only
had one value. I set up a text box in the details section that did the sum
and made it a running sum, then i set the text box in the report footer
section equal to the text box in hte details section.

thanks for the help.

tblDefectCount
DBA_part_machine1
tblDefectCount
DBA_part_customer1

I count four tables from the code you provided.

Whenever I get the ambiguous outer joins (and it don't make sense to me) I
remove the join and table/query (that I am trying to add and that it is
joining) and create a new query with the old query (that worked) in it, and
add the table/query that it wouldn't let me join earlier and it will now let
me join it the way I want.

Now that I have re-read your problem I wonder if it is as simple as an
expression on your report being too complicated...somehow I missed this the
first time. Check the underlying expressions on the report to see if any of
them are referencing some field on the table you say is not being used. If
it is not, maybe rethink the expressions that are being used on the report.
You say your query works but not the report--correct? Then I would say the
problem is in an expression in the report not the query.

Blanks in your query lead me to think that one of the common fields that are
used in the query is referencing the table that don't have records for that
field...do some records in that field have data?

Just crazy thoughts here...maybe?

thanks for the reply.
i had tried changing the relationships in the query itself between each of
the tables, using each of the different types. Each time i still got 28
records, instead of the 19, except for one setup where i received an error of
"ambiguous outer joins". And other than making the joins in the query with
the new table, it is not referenced in the query. the table is just used to
convert an external part number into an internal part number to get the
'parts_per_cycle', 'cycle_time', and 'crew_size' from the "DBA_part_machine1"
table. Which is what the previous table did, just the previous table had a
lot more in the way of fields, as well as duplicated data. So the query is
the same, and nothing on the report is referencing anything from the new
table, just as it wasn't referencing anything from the previous table. as
far as using another query to connect to the new table, i'm not sure what
your meaning or how to accomplish it. thanks again, and any other ideas/help
would be welcome.

Hi jkendrick75,

It sounds to me like you might have a type of relationship problem as well
as a query problem...I could be wrong ...

I say this because of the additional records. Do you have a table
associated with this query that has 9 records in it? Try changing your
relationship type between the tables in your query and see if it helps
(keeping the one-to-many relationship in mind). The "complex query" problem
can usually be solved by creating a new query and using the query that you
are in to connect to this other table that you have probably added...just a
quick guess...but its worth a try.

Good luck.

:

when i run the query as a query, i get the correct answers. however when i
try to open the report that runs off of the query, i am getting an error
"This expression is typed incorrectly, or it is too complex to be evaluated.
for example, a numeric expression may contain too many complicated elements.
Try simplifying the expression by assigning parts of the expression to
variables."

this report used to work, before i deleted a table, and added a different
table to the database. both tables had the same data, just one had several
other fields that were not necessary. the one that i'm using now just has
two fields. Using the one with the two fields was much easier to normalize,
as it had some duplicate values. When i ran the report, instead of showing
19 records like it should have, it was showing 28 records due to all of the
duplication of data in the three tables that the query works off of.

now some of the spaces, when ran as a query, are blank. i have set it up in
the report to automatically fill blanks with 1's, so as to not get other
display areas to show "DIV0#" or something like that.

The sql for the query is below:
---------------------------------------------------------------------------
SELECT tblDefectCount.Operator, tblDefectCount.Comments,
tblDefectCount.Code, tblDefectCount.PressNumber, tblDefectCount.MoldNumber,
tblDefectCount.ActualOps, tblDefectCount.UpTime, tblDefectCount.CycleTime,
tblDefectCount.ActualCavities, DBA_part_machine1.crew_size,
DBA_part_machine1.cycle_time, DBA_part_machine1.parts_per_cycle,
tblDefectCount.TotalPartsRan, tblDefectCount.TotalDefects,
tblDefectCount.DownTime
FROM (tblDefectCount LEFT JOIN DBA_part_customer1 ON
tblDefectCount.PartNumber = DBA_part_customer1.customer_part) LEFT JOIN
DBA_part_machine1 ON DBA_part_customer1.part = DBA_part_machine1.part
WHERE
(((tblDefectCount.WorkDate)=[forms]![frmDailyProductionReport].[txtDate]) AND
((tblDefectCount.Shift)=[forms]![frmDailyProductionReport].[txtShift]) AND
((tblDefectCount.Plant)=[forms]![frmdailyproductionReport].[txtPlant]))
GROUP BY tblDefectCount.Operator, tblDefectCount.Comments,
tblDefectCount.Code, tblDefectCount.PressNumber, tblDefectCount.MoldNumber,
tblDefectCount.ActualOps, tblDefectCount.UpTime, tblDefectCount.CycleTime,
tblDefectCount.ActualCavities, DBA_part_machine1.crew_size,
DBA_part_machine1.cycle_time, DBA_part_machine1.parts_per_cycle,
tblDefectCount.TotalPartsRan, tblDefectCount.TotalDefects,
tblDefectCount.DownTime;

 

[AccessAddict]

Good idea ... glad you solved your problem...I wish someone would solve
mine...I'm still waiting...

i was able to fix it. it was an expression. i copied the report to another
name, then removed all text boxes that had any kind of computations taking
place, then added text boxes from the old one to the new one one at a time.
i found out that i was trying to do a sum() to a set of numbers that only
had one value. I set up a text box in the details section that did the sum
and made it a running sum, then i set the text box in the report footer
section equal to the text box in hte details section.

thanks for the help.

tblDefectCount
DBA_part_machine1
tblDefectCount
DBA_part_customer1

I count four tables from the code you provided.

Whenever I get the ambiguous outer joins (and it don't make sense to me) I
remove the join and table/query (that I am trying to add and that it is
joining) and create a new query with the old query (that worked) in it, and
add the table/query that it wouldn't let me join earlier and it will now let
me join it the way I want.

Now that I have re-read your problem I wonder if it is as simple as an
expression on your report being too complicated...somehow I missed this the
first time. Check the underlying expressions on the report to see if any of
them are referencing some field on the table you say is not being used. If
it is not, maybe rethink the expressions that are being used on the report.
You say your query works but not the report--correct? Then I would say the
problem is in an expression in the report not the query.

Blanks in your query lead me to think that one of the common fields that are
used in the query is referencing the table that don't have records for that
field...do some records in that field have data?

Just crazy thoughts here...maybe?

thanks for the reply.
i had tried changing the relationships in the query itself between each of
the tables, using each of the different types. Each time i still got 28
records, instead of the 19, except for one setup where i received an error of
"ambiguous outer joins". And other than making the joins in the query with
the new table, it is not referenced in the query. the table is just used to
convert an external part number into an internal part number to get the
'parts_per_cycle', 'cycle_time', and 'crew_size' from the "DBA_part_machine1"
table. Which is what the previous table did, just the previous table had a
lot more in the way of fields, as well as duplicated data. So the query is
the same, and nothing on the report is referencing anything from the new
table, just as it wasn't referencing anything from the previous table. as
far as using another query to connect to the new table, i'm not sure what
your meaning or how to accomplish it. thanks again, and any other ideas/help
would be welcome.


:

Hi jkendrick75,

It sounds to me like you might have a type of relationship problem as well
as a query problem...I could be wrong ...

I say this because of the additional records. Do you have a table
associated with this query that has 9 records in it? Try changing your
relationship type between the tables in your query and see if it helps
(keeping the one-to-many relationship in mind). The "complex query" problem
can usually be solved by creating a new query and using the query that you
are in to connect to this other table that you have probably added...just a
quick guess...but its worth a try.

Good luck.

:

when i run the query as a query, i get the correct answers. however when i
try to open the report that runs off of the query, i am getting an error
"This expression is typed incorrectly, or it is too complex to be evaluated.
for example, a numeric expression may contain too many complicated elements.
Try simplifying the expression by assigning parts of the expression to
variables."

this report used to work, before i deleted a table, and added a different
table to the database. both tables had the same data, just one had several
other fields that were not necessary. the one that i'm using now just has
two fields. Using the one with the two fields was much easier to normalize,
as it had some duplicate values. When i ran the report, instead of showing
19 records like it should have, it was showing 28 records due to all of the
duplication of data in the three tables that the query works off of.

now some of the spaces, when ran as a query, are blank. i have set it up in
the report to automatically fill blanks with 1's, so as to not get other
display areas to show "DIV0#" or something like that.

The sql for the query is below:
---------------------------------------------------------------------------
SELECT tblDefectCount.Operator, tblDefectCount.Comments,
tblDefectCount.Code, tblDefectCount.PressNumber, tblDefectCount.MoldNumber,
tblDefectCount.ActualOps, tblDefectCount.UpTime, tblDefectCount.CycleTime,
tblDefectCount.ActualCavities, DBA_part_machine1.crew_size,
DBA_part_machine1.cycle_time, DBA_part_machine1.parts_per_cycle,
tblDefectCount.TotalPartsRan, tblDefectCount.TotalDefects,
tblDefectCount.DownTime
FROM (tblDefectCount LEFT JOIN DBA_part_customer1 ON
tblDefectCount.PartNumber = DBA_part_customer1.customer_part) LEFT JOIN
DBA_part_machine1 ON DBA_part_customer1.part = DBA_part_machine1.part
WHERE
(((tblDefectCount.WorkDate)=[forms]![frmDailyProductionReport].[txtDate]) AND
((tblDefectCount.Shift)=[forms]![frmDailyProductionReport].[txtShift]) AND
((tblDefectCount.Plant)=[forms]![frmdailyproductionReport].[txtPlant]))
GROUP BY tblDefectCount.Operator, tblDefectCount.Comments,
tblDefectCount.Code, tblDefectCount.PressNumber, tblDefectCount.MoldNumber,
tblDefectCount.ActualOps, tblDefectCount.UpTime, tblDefectCount.CycleTime,
tblDefectCount.ActualCavities, DBA_part_machine1.crew_size,
DBA_part_machine1.cycle_time, DBA_part_machine1.parts_per_cycle,
tblDefectCount.TotalPartsRan, tblDefectCount.TotalDefects,
tblDefectCount.DownTime;

 

[jkendrick75]

thanks for your help and i am sorry, i don't know how to do anything with
replication in access. good luck and hope somebody is able to help you.

Good idea ... glad you solved your problem...I wish someone would solve
mine...I'm still waiting...

i was able to fix it. it was an expression. i copied the report to another
name, then removed all text boxes that had any kind of computations taking
place, then added text boxes from the old one to the new one one at a time.
i found out that i was trying to do a sum() to a set of numbers that only
had one value. I set up a text box in the details section that did the sum
and made it a running sum, then i set the text box in the report footer
section equal to the text box in hte details section.

thanks for the help.

tblDefectCount
DBA_part_machine1
tblDefectCount
DBA_part_customer1

I count four tables from the code you provided.

Whenever I get the ambiguous outer joins (and it don't make sense to me) I
remove the join and table/query (that I am trying to add and that it is
joining) and create a new query with the old query (that worked) in it, and
add the table/query that it wouldn't let me join earlier and it will now let
me join it the way I want.

Now that I have re-read your problem I wonder if it is as simple as an
expression on your report being too complicated...somehow I missed this the
first time. Check the underlying expressions on the report to see if any of
them are referencing some field on the table you say is not being used. If
it is not, maybe rethink the expressions that are being used on the report.
You say your query works but not the report--correct? Then I would say the
problem is in an expression in the report not the query.

Blanks in your query lead me to think that one of the common fields that are
used in the query is referencing the table that don't have records for that
field...do some records in that field have data?

Just crazy thoughts here...maybe?
:

thanks for the reply.
i had tried changing the relationships in the query itself between each of
the tables, using each of the different types. Each time i still got 28
records, instead of the 19, except for one setup where i received an error of
"ambiguous outer joins". And other than making the joins in the query with
the new table, it is not referenced in the query. the table is just used to
convert an external part number into an internal part number to get the
'parts_per_cycle', 'cycle_time', and 'crew_size' from the "DBA_part_machine1"
table. Which is what the previous table did, just the previous table had a
lot more in the way of fields, as well as duplicated data. So the query is
the same, and nothing on the report is referencing anything from the new
table, just as it wasn't referencing anything from the previous table. as
far as using another query to connect to the new table, i'm not sure what
your meaning or how to accomplish it. thanks again, and any other ideas/help
would be welcome.


:

Hi jkendrick75,

It sounds to me like you might have a type of relationship problem as well
as a query problem...I could be wrong ...

I say this because of the additional records. Do you have a table
associated with this query that has 9 records in it? Try changing your
relationship type between the tables in your query and see if it helps
(keeping the one-to-many relationship in mind). The "complex query" problem
can usually be solved by creating a new query and using the query that you
are in to connect to this other table that you have probably added...just a
quick guess...but its worth a try.

Good luck.

:

when i run the query as a query, i get the correct answers. however when i
try to open the report that runs off of the query, i am getting an error
"This expression is typed incorrectly, or it is too complex to be evaluated.
for example, a numeric expression may contain too many complicated elements.
Try simplifying the expression by assigning parts of the expression to
variables."

this report used to work, before i deleted a table, and added a different
table to the database. both tables had the same data, just one had several
other fields that were not necessary. the one that i'm using now just has
two fields. Using the one with the two fields was much easier to normalize,
as it had some duplicate values. When i ran the report, instead of showing
19 records like it should have, it was showing 28 records due to all of the
duplication of data in the three tables that the query works off of.

now some of the spaces, when ran as a query, are blank. i have set it up in
the report to automatically fill blanks with 1's, so as to not get other
display areas to show "DIV0#" or something like that.

The sql for the query is below:
---------------------------------------------------------------------------
SELECT tblDefectCount.Operator, tblDefectCount.Comments,
tblDefectCount.Code, tblDefectCount.PressNumber, tblDefectCount.MoldNumber,
tblDefectCount.ActualOps, tblDefectCount.UpTime, tblDefectCount.CycleTime,
tblDefectCount.ActualCavities, DBA_part_machine1.crew_size,
DBA_part_machine1.cycle_time, DBA_part_machine1.parts_per_cycle,
tblDefectCount.TotalPartsRan, tblDefectCount.TotalDefects,
tblDefectCount.DownTime
FROM (tblDefectCount LEFT JOIN DBA_part_customer1 ON
tblDefectCount.PartNumber = DBA_part_customer1.customer_part) LEFT JOIN
DBA_part_machine1 ON DBA_part_customer1.part = DBA_part_machine1.part
WHERE
(((tblDefectCount.WorkDate)=[forms]![frmDailyProductionReport].[txtDate]) AND
((tblDefectCount.Shift)=[forms]![frmDailyProductionReport].[txtShift]) AND
((tblDefectCount.Plant)=[forms]![frmdailyproductionReport].[txtPlant]))
GROUP BY tblDefectCount.Operator, tblDefectCount.Comments,
tblDefectCount.Code, tblDefectCount.PressNumber, tblDefectCount.MoldNumber,
tblDefectCount.ActualOps, tblDefectCount.UpTime, tblDefectCount.CycleTime,
tblDefectCount.ActualCavities, DBA_part_machine1.crew_size,
DBA_part_machine1.cycle_time, DBA_part_machine1.parts_per_cycle,
tblDefectCount.TotalPartsRan, tblDefectCount.TotalDefects,
tblDefectCount.DownTime;