Query - Calculating field

[sahafi]

Hi All,

I have 2 tables, that I need to creat a relationship between them. Table 1
has: location #, Year, Period, Week, Product_Class, Prod_Flavor, Prod_size,
Total LB. Table 2 has: Location #, Year, Period, Week, Product_Class, Hours,
DT_Hours.
I need to set up a query on tbl1 so as to sum up all the pounds for specific
product class for a given week (sum all pounds regardless of flavor or size).
Because currently you can come up with more than 20 rows of data for one
week, while in tbl2 only one row of data for each week.
I have tried to set up a query without selecting the flavor and size fields,
but when I ran it, it didn't sum up the pounds, I end up with the same amount
of rows on the query as it is on the table. Can anyone help with this.
My second question, after figuring step one to make a primary key
fromLocation#+Yr+PD+WK+Product_Class) because it seems like that the only
way to have a unique indentifier, unless someone have a better idea.

Thanks.

 

[Jerry Whittle]

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.

 

[sahafi]

Sorry for the late reply. Here's the SQL:

SELECT [Tbl1].LOC, [Tbl1].YEAR, [Tbl1].PD, [Tbl1].WK, [Tbl1].PROD_CLASS,
[Tbl1].LBS
FROM [Tbl1];

Thanks.

 

[John Spencer]

I see no aggregate functions in your query.

SELECT [Tbl1].LOC, [Tbl1].YEAR, [Tbl1].PD, [Tbl1].WK, [Tbl1].PROD_CLASS,
SUM([Tbl1].LBS) as TotalPounds
FROM [Tbl1]
GROUP BY [Tbl1].LOC, [Tbl1].YEAR, [Tbl1].PD, [Tbl1].WK, [Tbl1].PROD_CLASS

Hint: If you are working in the query grid, select View: Totals from the
menu while working on the query in design view.

Sorry for the late reply. Here's the SQL:

SELECT [Tbl1].LOC, [Tbl1].YEAR, [Tbl1].PD, [Tbl1].WK, [Tbl1].PROD_CLASS,
[Tbl1].LBS
FROM [Tbl1];

Thanks.
--
when u change the way u look @ things, the things u look at change.

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.

 

[sahafi]

Thank you John. After adding the aggregate clause, it works. But I still
can't create a query from tbl2 and my new query (that's based on tbl1). I was
able to create the relationship OK by using 5-fields primary key. I went
ahead and created a query from tbl2 so as my new query will be generated from
the two new queries but still will not work. I keep getting this error: (You
have chosen fields from record sources which the wizard cannot connect. You
may have chosen fields from a table and from a query based on that table. If
so, try choose fields from only a table, or only a query). But I have
selected fields from different sources. I have selected all the 5 fields for
the primary key plus the total pounds field from the first query. Then I have
selected my running hours and downtime hours from the other table/query. Any
idea?

Thanks.
--
when u change the way u look @ things, the things u look at change.

I see no aggregate functions in your query.

SELECT [Tbl1].LOC, [Tbl1].YEAR, [Tbl1].PD, [Tbl1].WK, [Tbl1].PROD_CLASS,
SUM([Tbl1].LBS) as TotalPounds
FROM [Tbl1]
GROUP BY [Tbl1].LOC, [Tbl1].YEAR, [Tbl1].PD, [Tbl1].WK, [Tbl1].PROD_CLASS

Hint: If you are working in the query grid, select View: Totals from the
menu while working on the query in design view.

Sorry for the late reply. Here's the SQL:

SELECT [Tbl1].LOC, [Tbl1].YEAR, [Tbl1].PD, [Tbl1].WK, [Tbl1].PROD_CLASS,
[Tbl1].LBS
FROM [Tbl1];

Thanks.
--
when u change the way u look @ things, the things u look at change.

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
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


:

Hi All,

I have 2 tables, that I need to creat a relationship between them.
Table 1
has: location #, Year, Period, Week, Product_Class, Prod_Flavor,
Prod_size,
Total LB. Table 2 has: Location #, Year, Period, Week, Product_Class,
Hours,
DT_Hours.
I need to set up a query on tbl1 so as to sum up all the pounds for
specific
product class for a given week (sum all pounds regardless of flavor or
size).
Because currently you can come up with more than 20 rows of data for
one
week, while in tbl2 only one row of data for each week.
I have tried to set up a query without selecting the flavor and size
fields,
but when I ran it, it didn't sum up the pounds, I end up with the same
amount
of rows on the query as it is on the table. Can anyone help with this.
My second question, after figuring step one to make a primary key
fromLocation#+Yr+PD+WK+Product_Class) because it seems like that the
only
way to have a unique indentifier, unless someone have a better idea.

Thanks.

 

[sahafi]

Never mind. I managed to run a new query from my original tables with all the
criteria that I needed.

Thanks for the help.


--
when u change the way u look @ things, the things u look at change.

Thank you John. After adding the aggregate clause, it works. But I still
can't create a query from tbl2 and my new query (that's based on tbl1). I was
able to create the relationship OK by using 5-fields primary key. I went
ahead and created a query from tbl2 so as my new query will be generated from
the two new queries but still will not work. I keep getting this error: (You
have chosen fields from record sources which the wizard cannot connect. You
may have chosen fields from a table and from a query based on that table. If
so, try choose fields from only a table, or only a query). But I have
selected fields from different sources. I have selected all the 5 fields for
the primary key plus the total pounds field from the first query. Then I have
selected my running hours and downtime hours from the other table/query. Any
idea?

Thanks.
--
when u change the way u look @ things, the things u look at change.

I see no aggregate functions in your query.

SELECT [Tbl1].LOC, [Tbl1].YEAR, [Tbl1].PD, [Tbl1].WK, [Tbl1].PROD_CLASS,
SUM([Tbl1].LBS) as TotalPounds
FROM [Tbl1]
GROUP BY [Tbl1].LOC, [Tbl1].YEAR, [Tbl1].PD, [Tbl1].WK, [Tbl1].PROD_CLASS

Hint: If you are working in the query grid, select View: Totals from the
menu while working on the query in design view.

Sorry for the late reply. Here's the SQL:

SELECT [Tbl1].LOC, [Tbl1].YEAR, [Tbl1].PD, [Tbl1].WK, [Tbl1].PROD_CLASS,
[Tbl1].LBS
FROM [Tbl1];

Thanks.
--
when u change the way u look @ things, the things u look at change.


:

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
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


:

Hi All,

I have 2 tables, that I need to creat a relationship between them.
Table 1
has: location #, Year, Period, Week, Product_Class, Prod_Flavor,
Prod_size,
Total LB. Table 2 has: Location #, Year, Period, Week, Product_Class,
Hours,
DT_Hours.
I need to set up a query on tbl1 so as to sum up all the pounds for
specific
product class for a given week (sum all pounds regardless of flavor or
size).
Because currently you can come up with more than 20 rows of data for
one
week, while in tbl2 only one row of data for each week.
I have tried to set up a query without selecting the flavor and size
fields,
but when I ran it, it didn't sum up the pounds, I end up with the same
amount
of rows on the query as it is on the table. Can anyone help with this.
My second question, after figuring step one to make a primary key
fromLocation#+Yr+PD+WK+Product_Class) because it seems like that the
only
way to have a unique indentifier, unless someone have a better idea.

Thanks.