use a results in a Query to compare with information in a table

[SailorRoy]

This is what I am trying to accomplish: I do a Query on one table that
returns a result of the combined time and number of parts produced for that
time. Then I get an expression that results in number of parts produced per
hour. This is all good but now I need it to compare the “part numberâ€
returned in the Query with the “part number†in the Parts table and compare
the “parts per hour produced†with the “parts per hour estimated†and return
as % efficient. How do I that information from the parts table? Thanks for
any help. Access 2007

 

[Jeff Boyce]

We're not there, we can't see your PC. We don't (necessarily) know what you
mean by "combined time".

You didn't post your query's SQL statement, nor your data/table structure.

More specific description will probably lead to more specific suggestions.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[SailorRoy]

"Combined time" is the total of the time charged and also total of parts
produced. The time and parts produced are recorded daily and then a query is
generated to track the total time/parts etc. Then I calculate the parts per
hour. I have a table that has part information created from the quotes for
these parts that has the material cost estimates and labor estimates etc.,
this includes the parts per hour. I want to compare the actual time of the
quoted time to actual time for a production report.

SELECT [Time Sheet].JobNumber, Sum([Time Sheet].TimeCharged) AS
SumOfTimeCharged, Sum([Time Sheet].OrderQuanity) AS SumOfOrderQuanity,
Sum([Time Sheet].QuanityProduced) AS SumOfQuanityProduced, Sum([Time
Sheet].ScrapNumberParts) AS SumOfScrapNumberParts, Sum([Time
Sheet].JobSetUpTime) AS SumOfJobSetUpTime, First([Time Sheet].PartNumber) AS
FirstOfPartNumber,
([SumOfQuanityProduced]-[SumOfScrapNumberParts])/[SumOfTimeCharged] AS Expr1
FROM [Time Sheet], [Part Numbers]
GROUP BY [Time Sheet].JobNumber
ORDER BY [Time Sheet].JobNumber;

I am also trying to get the totals and I don't have that right either.

Roy LaFever

 

[Jeff Boyce]

Roy

Sorry, but I'm still having trouble visualizing the data you are starting
with. Since queries are based on tables (and on other queries), it would
help me "see" your picture if you provided a description of the tables and
the fields ... perhaps something like:

tblPerson
PersonID (PK)
FirstName
MI
LastName
DOB

and an example of the kind of data you are storing in the tables:

17
Jeff
X
Boyce
1/1/2001

Regards

Jeff Boyce
Microsoft Office/Access MVP

"Combined time" is the total of the time charged and also total of parts
produced. The time and parts produced are recorded daily and then a query
is
generated to track the total time/parts etc. Then I calculate the parts
per
hour. I have a table that has part information created from the quotes
for
these parts that has the material cost estimates and labor estimates etc.,
this includes the parts per hour. I want to compare the actual time of
the
quoted time to actual time for a production report.

SELECT [Time Sheet].JobNumber, Sum([Time Sheet].TimeCharged) AS
SumOfTimeCharged, Sum([Time Sheet].OrderQuanity) AS SumOfOrderQuanity,
Sum([Time Sheet].QuanityProduced) AS SumOfQuanityProduced, Sum([Time
Sheet].ScrapNumberParts) AS SumOfScrapNumberParts, Sum([Time
Sheet].JobSetUpTime) AS SumOfJobSetUpTime, First([Time Sheet].PartNumber)
AS
FirstOfPartNumber,
([SumOfQuanityProduced]-[SumOfScrapNumberParts])/[SumOfTimeCharged] AS
Expr1
FROM [Time Sheet], [Part Numbers]
GROUP BY [Time Sheet].JobNumber
ORDER BY [Time Sheet].JobNumber;

I am also trying to get the totals and I don't have that right either.

Roy LaFever

We're not there, we can't see your PC. We don't (necessarily) know what
you
mean by "combined time".

You didn't post your query's SQL statement, nor your data/table
structure.

More specific description will probably lead to more specific
suggestions.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[SailorRoy]

I have two tables: One table “Job Time Table†that has fields for the
JobOrderNumber, PartNumber, TimeCharged, PartsProduced , plus other
information such as date, scrap, etc. When we receive an order we issue it a
Job Order Number. We then manufacture the parts over a period of days or
weeks. Each day the time and information is enter into the database “Job
Time Table†I now want to run a report or query to total the Parts Produced,
total time etc. and compare it to the allowed time from the next table “Part
Numbers Tableâ€. The “Part Numbers Table†has the part numbers and the
EstimatedTime, EstimatedMaterails, etc. allowed for each part with other
information.
How do I make this query? I can get part of it but how do I get it to
access the information from the Part Number table and compare. I am totaling
the time etc. to the Job Number information but need to get the estimated
time by the part number. I am using Access 2007.

Roy

Sorry, but I'm still having trouble visualizing the data you are starting
with. Since queries are based on tables (and on other queries), it would
help me "see" your picture if you provided a description of the tables and
the fields ... perhaps something like:

tblPerson
PersonID (PK)
FirstName
MI
LastName
DOB

and an example of the kind of data you are storing in the tables:

17
Jeff
X
Boyce
1/1/2001

Regards

Jeff Boyce
Microsoft Office/Access MVP

"Combined time" is the total of the time charged and also total of parts
produced. The time and parts produced are recorded daily and then a query
is
generated to track the total time/parts etc. Then I calculate the parts
per
hour. I have a table that has part information created from the quotes
for
these parts that has the material cost estimates and labor estimates etc.,
this includes the parts per hour. I want to compare the actual time of
the
quoted time to actual time for a production report.

SELECT [Time Sheet].JobNumber, Sum([Time Sheet].TimeCharged) AS
SumOfTimeCharged, Sum([Time Sheet].OrderQuanity) AS SumOfOrderQuanity,
Sum([Time Sheet].QuanityProduced) AS SumOfQuanityProduced, Sum([Time
Sheet].ScrapNumberParts) AS SumOfScrapNumberParts, Sum([Time
Sheet].JobSetUpTime) AS SumOfJobSetUpTime, First([Time Sheet].PartNumber)
AS
FirstOfPartNumber,
([SumOfQuanityProduced]-[SumOfScrapNumberParts])/[SumOfTimeCharged] AS
Expr1
FROM [Time Sheet], [Part Numbers]
GROUP BY [Time Sheet].JobNumber
ORDER BY [Time Sheet].JobNumber;

I am also trying to get the totals and I don't have that right either.

Roy LaFever

We're not there, we can't see your PC. We don't (necessarily) know what
you
mean by "combined time".

You didn't post your query's SQL statement, nor your data/table
structure.

More specific description will probably lead to more specific
suggestions.

Regards

Jeff Boyce
Microsoft Office/Access MVP

This is what I am trying to accomplish: I do a Query on one table that
returns a result of the combined time and number of parts produced for
that
time. Then I get an expression that results in number of parts
produced
per
hour. This is all good but now I need it to compare the "part number"
returned in the Query with the "part number" in the Parts table and
compare
the "parts per hour produced" with the "parts per hour estimated" and
return
as % efficient. How do I that information from the parts table?
Thanks
for
any help. Access 2007

 

[Jeff Boyce]

Based on your description, your JobTimeTable has, as one field, PartNumber.
And your PartNumbersTable has, as one field, PartNumber.

This sounds like it calls for creating a query and joining these two tables
on the PartNumber fields. Or, if you are doing a query first to get actual
parts per hour, you could join THAT query to the PartNumbersTable.

By the way, Access will be confused if you leave spaces between words in the
title, in fieldname titles, and in Access objects in general. Either use
brackets ("[ ]") around them or use CamelCase without the space.

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have two tables: One table "Job Time Table" that has fields for the
JobOrderNumber, PartNumber, TimeCharged, PartsProduced , plus other
information such as date, scrap, etc. When we receive an order we issue
it a
Job Order Number. We then manufacture the parts over a period of days or
weeks. Each day the time and information is enter into the database "Job
Time Table" I now want to run a report or query to total the Parts
Produced,
total time etc. and compare it to the allowed time from the next table
"Part
Numbers Table". The "Part Numbers Table" has the part numbers and the
EstimatedTime, EstimatedMaterails, etc. allowed for each part with other
information.
How do I make this query? I can get part of it but how do I get it to
access the information from the Part Number table and compare. I am
totaling
the time etc. to the Job Number information but need to get the estimated
time by the part number. I am using Access 2007.

Roy

Sorry, but I'm still having trouble visualizing the data you are starting
with. Since queries are based on tables (and on other queries), it would
help me "see" your picture if you provided a description of the tables
and
the fields ... perhaps something like:

tblPerson
PersonID (PK)
FirstName
MI
LastName
DOB

and an example of the kind of data you are storing in the tables:

17
Jeff
X
Boyce
1/1/2001

Regards

Jeff Boyce
Microsoft Office/Access MVP

"Combined time" is the total of the time charged and also total of
parts
produced. The time and parts produced are recorded daily and then a
query
is
generated to track the total time/parts etc. Then I calculate the
parts
per
hour. I have a table that has part information created from the quotes
for
these parts that has the material cost estimates and labor estimates
etc.,
this includes the parts per hour. I want to compare the actual time of
the
quoted time to actual time for a production report.

SELECT [Time Sheet].JobNumber, Sum([Time Sheet].TimeCharged) AS
SumOfTimeCharged, Sum([Time Sheet].OrderQuanity) AS SumOfOrderQuanity,
Sum([Time Sheet].QuanityProduced) AS SumOfQuanityProduced, Sum([Time
Sheet].ScrapNumberParts) AS SumOfScrapNumberParts, Sum([Time
Sheet].JobSetUpTime) AS SumOfJobSetUpTime, First([Time
Sheet].PartNumber)
AS
FirstOfPartNumber,
([SumOfQuanityProduced]-[SumOfScrapNumberParts])/[SumOfTimeCharged] AS
Expr1
FROM [Time Sheet], [Part Numbers]
GROUP BY [Time Sheet].JobNumber
ORDER BY [Time Sheet].JobNumber;

I am also trying to get the totals and I don't have that right either.

Roy LaFever


:

We're not there, we can't see your PC. We don't (necessarily) know
what
you
mean by "combined time".

You didn't post your query's SQL statement, nor your data/table
structure.

More specific description will probably lead to more specific
suggestions.

Regards

Jeff Boyce
Microsoft Office/Access MVP

This is what I am trying to accomplish: I do a Query on one table
that
returns a result of the combined time and number of parts produced
for
that
time. Then I get an expression that results in number of parts
produced
per
hour. This is all good but now I need it to compare the "part
number"
returned in the Query with the "part number" in the Parts table and
compare
the "parts per hour produced" with the "parts per hour estimated"
and
return
as % efficient. How do I that information from the parts table?
Thanks
for
any help. Access 2007