Query set-up with two tables


A

AccessKay

Good Morning Everyone,

I’m learning and creating a database at the same time. I first added the
table Trans_Mstr_Lbr that is an import from Excel that contains all my data
related to labor. I built queries and reports based on this table. But then
I wanted to add ODC which is another import from Excel. I can’t really
establish a unique relationship between the two tables, though I thought
possibly about the date field. They both have the same month/year in common.
My goal is to create a query and report that displays the Labor Cost,ODC
Cost, and then the sum of the two, grouped by Category, Group, and Product.
Since these tables have such similar fields, I thought maybe I should combine
them??? Though, I’d rather keep them separately. I played around with
trying to create some queries and adding common tables and joins…and then
decided that I was getting nowhere and thought I’d ask the experts. What
should I do in this case? Please feel free to elaborate and you’re help is
greatly appreciated.

tblTran_Mstr_Lbr
ID (auto#)
TransDate
Category
Group
Product
Lbr_Cost
OH_Cost

tblTrans_Mstr_ODC
ID (auto#)
TransDate
Category
Group
Product
ODC_Cost_Category
ODC_Cost

Thanks,
Kay
 
Ad

Advertisements

K

KARL DEWEY

Use a union query --
SELECT TransDate, Category, Group, Product, "ODC_Cost" AS [Cost_Type],
ODC_Cost AS Cost, ODC_Cost_Category
FROM tblTrans_Mstr_ODC
UNION ALLSELECT TransDate, Category, Group, Product, "Labor_Cost" AS
[Cost_Type], Lbr_Cost AS Cost, OH_Cost, Null
FROM tblTran_Mstr_Lbr;
 
A

AccessKay

Thanks for your reply.

I read about Union queries but didn’t know that I could use one in this
case. I actually need the Sum of Lbr_Cost and OH_Cost but I guess I could
create another query to add the two and then try the Union again. Am I
correct? But I did receive an Error message when I tested this SQL as
is…â€Invalid SQL statement; expected DELETE, INSERT, PROCEDURE….etc. What am
I missing?


KARL DEWEY said:
Use a union query --
SELECT TransDate, Category, Group, Product, "ODC_Cost" AS [Cost_Type],
ODC_Cost AS Cost, ODC_Cost_Category
FROM tblTrans_Mstr_ODC
UNION ALLSELECT TransDate, Category, Group, Product, "Labor_Cost" AS
[Cost_Type], Lbr_Cost AS Cost, OH_Cost, Null
FROM tblTran_Mstr_Lbr;

--
Build a little, test a little.


AccessKay said:
Good Morning Everyone,

I’m learning and creating a database at the same time. I first added the
table Trans_Mstr_Lbr that is an import from Excel that contains all my data
related to labor. I built queries and reports based on this table. But then
I wanted to add ODC which is another import from Excel. I can’t really
establish a unique relationship between the two tables, though I thought
possibly about the date field. They both have the same month/year in common.
My goal is to create a query and report that displays the Labor Cost,ODC
Cost, and then the sum of the two, grouped by Category, Group, and Product.
Since these tables have such similar fields, I thought maybe I should combine
them??? Though, I’d rather keep them separately. I played around with
trying to create some queries and adding common tables and joins…and then
decided that I was getting nowhere and thought I’d ask the experts. What
should I do in this case? Please feel free to elaborate and you’re help is
greatly appreciated.

tblTran_Mstr_Lbr
ID (auto#)
TransDate
Category
Group
Product
Lbr_Cost
OH_Cost

tblTrans_Mstr_ODC
ID (auto#)
TransDate
Category
Group
Product
ODC_Cost_Category
ODC_Cost

Thanks,
Kay
 
K

KARL DEWEY

Post back your SQL, sometimes copying and pasting post introduces errors.

--
Build a little, test a little.


AccessKay said:
Thanks for your reply.

I read about Union queries but didn’t know that I could use one in this
case. I actually need the Sum of Lbr_Cost and OH_Cost but I guess I could
create another query to add the two and then try the Union again. Am I
correct? But I did receive an Error message when I tested this SQL as
is…â€Invalid SQL statement; expected DELETE, INSERT, PROCEDURE….etc. What am
I missing?


KARL DEWEY said:
Use a union query --
SELECT TransDate, Category, Group, Product, "ODC_Cost" AS [Cost_Type],
ODC_Cost AS Cost, ODC_Cost_Category
FROM tblTrans_Mstr_ODC
UNION ALLSELECT TransDate, Category, Group, Product, "Labor_Cost" AS
[Cost_Type], Lbr_Cost AS Cost, OH_Cost, Null
FROM tblTran_Mstr_Lbr;

--
Build a little, test a little.


AccessKay said:
Good Morning Everyone,

I’m learning and creating a database at the same time. I first added the
table Trans_Mstr_Lbr that is an import from Excel that contains all my data
related to labor. I built queries and reports based on this table. But then
I wanted to add ODC which is another import from Excel. I can’t really
establish a unique relationship between the two tables, though I thought
possibly about the date field. They both have the same month/year in common.
My goal is to create a query and report that displays the Labor Cost,ODC
Cost, and then the sum of the two, grouped by Category, Group, and Product.
Since these tables have such similar fields, I thought maybe I should combine
them??? Though, I’d rather keep them separately. I played around with
trying to create some queries and adding common tables and joins…and then
decided that I was getting nowhere and thought I’d ask the experts. What
should I do in this case? Please feel free to elaborate and you’re help is
greatly appreciated.

tblTran_Mstr_Lbr
ID (auto#)
TransDate
Category
Group
Product
Lbr_Cost
OH_Cost

tblTrans_Mstr_ODC
ID (auto#)
TransDate
Category
Group
Product
ODC_Cost_Category
ODC_Cost

Thanks,
Kay
 
A

AccessKay

Karl,
I double checked the names and the only changes I made was to the table
name. Exact table name excludes the "tbl".

SELECT TransDate, Category, Group, Product, "ODC_Cost" AS [Cost_Type],
ODC_Cost AS Cost, ODC_Cost_Category
FROM Trans_Mstr_ODC
UNION ALLSELECT TransDate, Category, Group, Product, "Labor_Cost" AS
[Cost_Type], Lbr_Cost AS Cost, OH_Cost, Null
FROM Tran_Mstr_LBR;

Thanks,
Kay



KARL DEWEY said:
Post back your SQL, sometimes copying and pasting post introduces errors.

--
Build a little, test a little.


AccessKay said:
Thanks for your reply.

I read about Union queries but didn’t know that I could use one in this
case. I actually need the Sum of Lbr_Cost and OH_Cost but I guess I could
create another query to add the two and then try the Union again. Am I
correct? But I did receive an Error message when I tested this SQL as
is…â€Invalid SQL statement; expected DELETE, INSERT, PROCEDURE….etc. What am
I missing?


KARL DEWEY said:
Use a union query --
SELECT TransDate, Category, Group, Product, "ODC_Cost" AS [Cost_Type],
ODC_Cost AS Cost, ODC_Cost_Category
FROM tblTrans_Mstr_ODC
UNION ALLSELECT TransDate, Category, Group, Product, "Labor_Cost" AS
[Cost_Type], Lbr_Cost AS Cost, OH_Cost, Null
FROM tblTran_Mstr_Lbr;

--
Build a little, test a little.


:

Good Morning Everyone,

I’m learning and creating a database at the same time. I first added the
table Trans_Mstr_Lbr that is an import from Excel that contains all my data
related to labor. I built queries and reports based on this table. But then
I wanted to add ODC which is another import from Excel. I can’t really
establish a unique relationship between the two tables, though I thought
possibly about the date field. They both have the same month/year in common.
My goal is to create a query and report that displays the Labor Cost,ODC
Cost, and then the sum of the two, grouped by Category, Group, and Product.
Since these tables have such similar fields, I thought maybe I should combine
them??? Though, I’d rather keep them separately. I played around with
trying to create some queries and adding common tables and joins…and then
decided that I was getting nowhere and thought I’d ask the experts. What
should I do in this case? Please feel free to elaborate and you’re help is
greatly appreciated.

tblTran_Mstr_Lbr
ID (auto#)
TransDate
Category
Group
Product
Lbr_Cost
OH_Cost

tblTrans_Mstr_ODC
ID (auto#)
TransDate
Category
Group
Product
ODC_Cost_Category
ODC_Cost

Thanks,
Kay
 
D

Duane Hookom

You are missing at least one space and the number of fields/columns doesn't
match.

SELECT TransDate, Category, Group, Product, "ODC_Cost" AS [Cost_Type],
ODC_Cost AS Cost, ODC_Cost_Category
FROM Trans_Mstr_ODC
UNION ALL SELECT TransDate, Category, Group, Product, "Labor_Cost" AS
[Cost_Type], Lbr_Cost AS Cost, OH_Cost, Null
FROM Tran_Mstr_LBR;

--
Duane Hookom
Microsoft Access MVP


AccessKay said:
Karl,
I double checked the names and the only changes I made was to the table
name. Exact table name excludes the "tbl".

SELECT TransDate, Category, Group, Product, "ODC_Cost" AS [Cost_Type],
ODC_Cost AS Cost, ODC_Cost_Category
FROM Trans_Mstr_ODC
UNION ALLSELECT TransDate, Category, Group, Product, "Labor_Cost" AS
[Cost_Type], Lbr_Cost AS Cost, OH_Cost, Null
FROM Tran_Mstr_LBR;

Thanks,
Kay



KARL DEWEY said:
Post back your SQL, sometimes copying and pasting post introduces errors.

--
Build a little, test a little.


AccessKay said:
Thanks for your reply.

I read about Union queries but didn’t know that I could use one in this
case. I actually need the Sum of Lbr_Cost and OH_Cost but I guess I could
create another query to add the two and then try the Union again. Am I
correct? But I did receive an Error message when I tested this SQL as
is…â€Invalid SQL statement; expected DELETE, INSERT, PROCEDURE….etc. What am
I missing?


:

Use a union query --
SELECT TransDate, Category, Group, Product, "ODC_Cost" AS [Cost_Type],
ODC_Cost AS Cost, ODC_Cost_Category
FROM tblTrans_Mstr_ODC
UNION ALLSELECT TransDate, Category, Group, Product, "Labor_Cost" AS
[Cost_Type], Lbr_Cost AS Cost, OH_Cost, Null
FROM tblTran_Mstr_Lbr;

--
Build a little, test a little.


:

Good Morning Everyone,

I’m learning and creating a database at the same time. I first added the
table Trans_Mstr_Lbr that is an import from Excel that contains all my data
related to labor. I built queries and reports based on this table. But then
I wanted to add ODC which is another import from Excel. I can’t really
establish a unique relationship between the two tables, though I thought
possibly about the date field. They both have the same month/year in common.
My goal is to create a query and report that displays the Labor Cost,ODC
Cost, and then the sum of the two, grouped by Category, Group, and Product.
Since these tables have such similar fields, I thought maybe I should combine
them??? Though, I’d rather keep them separately. I played around with
trying to create some queries and adding common tables and joins…and then
decided that I was getting nowhere and thought I’d ask the experts. What
should I do in this case? Please feel free to elaborate and you’re help is
greatly appreciated.

tblTran_Mstr_Lbr
ID (auto#)
TransDate
Category
Group
Product
Lbr_Cost
OH_Cost

tblTrans_Mstr_ODC
ID (auto#)
TransDate
Category
Group
Product
ODC_Cost_Category
ODC_Cost

Thanks,
Kay
 
Ad

Advertisements

A

AccessKay

Duane,

I’m not sure I understand. Are you saying that I can’t do a Union Query?
If so, please see my first post. If that’s not what you’re saying then I’m
really confused because the SQL you sent back looks exactly like the SQL that
I sent. Please forgive me if I'm missing something here.

Duane Hookom said:
You are missing at least one space and the number of fields/columns doesn't
match.

SELECT TransDate, Category, Group, Product, "ODC_Cost" AS [Cost_Type],
ODC_Cost AS Cost, ODC_Cost_Category
FROM Trans_Mstr_ODC
UNION ALL SELECT TransDate, Category, Group, Product, "Labor_Cost" AS
[Cost_Type], Lbr_Cost AS Cost, OH_Cost, Null
FROM Tran_Mstr_LBR;

--
Duane Hookom
Microsoft Access MVP


AccessKay said:
Karl,
I double checked the names and the only changes I made was to the table
name. Exact table name excludes the "tbl".

SELECT TransDate, Category, Group, Product, "ODC_Cost" AS [Cost_Type],
ODC_Cost AS Cost, ODC_Cost_Category
FROM Trans_Mstr_ODC
UNION ALLSELECT TransDate, Category, Group, Product, "Labor_Cost" AS
[Cost_Type], Lbr_Cost AS Cost, OH_Cost, Null
FROM Tran_Mstr_LBR;

Thanks,
Kay



KARL DEWEY said:
Post back your SQL, sometimes copying and pasting post introduces errors.

--
Build a little, test a little.


:

Thanks for your reply.

I read about Union queries but didn’t know that I could use one in this
case. I actually need the Sum of Lbr_Cost and OH_Cost but I guess I could
create another query to add the two and then try the Union again. Am I
correct? But I did receive an Error message when I tested this SQL as
is…â€Invalid SQL statement; expected DELETE, INSERT, PROCEDURE….etc. What am
I missing?


:

Use a union query --
SELECT TransDate, Category, Group, Product, "ODC_Cost" AS [Cost_Type],
ODC_Cost AS Cost, ODC_Cost_Category
FROM tblTrans_Mstr_ODC
UNION ALLSELECT TransDate, Category, Group, Product, "Labor_Cost" AS
[Cost_Type], Lbr_Cost AS Cost, OH_Cost, Null
FROM tblTran_Mstr_Lbr;

--
Build a little, test a little.


:

Good Morning Everyone,

I’m learning and creating a database at the same time. I first added the
table Trans_Mstr_Lbr that is an import from Excel that contains all my data
related to labor. I built queries and reports based on this table. But then
I wanted to add ODC which is another import from Excel. I can’t really
establish a unique relationship between the two tables, though I thought
possibly about the date field. They both have the same month/year in common.
My goal is to create a query and report that displays the Labor Cost,ODC
Cost, and then the sum of the two, grouped by Category, Group, and Product.
Since these tables have such similar fields, I thought maybe I should combine
them??? Though, I’d rather keep them separately. I played around with
trying to create some queries and adding common tables and joins…and then
decided that I was getting nowhere and thought I’d ask the experts. What
should I do in this case? Please feel free to elaborate and you’re help is
greatly appreciated.

tblTran_Mstr_Lbr
ID (auto#)
TransDate
Category
Group
Product
Lbr_Cost
OH_Cost

tblTrans_Mstr_ODC
ID (auto#)
TransDate
Category
Group
Product
ODC_Cost_Category
ODC_Cost

Thanks,
Kay
 
J

John W. Vinson

Karl,
I double checked the names and the only changes I made was to the table
name. Exact table name excludes the "tbl".

SELECT TransDate, Category, Group, Product, "ODC_Cost" AS [Cost_Type],
ODC_Cost AS Cost, ODC_Cost_Category
FROM Trans_Mstr_ODC
UNION ALLSELECT TransDate, Category, Group, Product, "Labor_Cost" AS
[Cost_Type], Lbr_Cost AS Cost, OH_Cost, Null
FROM Tran_Mstr_LBR;

Missing a blank: there should be a blank between UNION ALL and SELECT.
 
K

KARL DEWEY

I had one more field in the second part than the first. Try it now --
SELECT TransDate, Category, Group, Product, "ODC_Cost" AS [Cost_Type],
ODC_Cost AS Cost, ODC_Cost_Category, Null AS OH_Cost
FROM Trans_Mstr_ODC
UNION ALL SELECT TransDate, Category, Group, Product, "Labor_Cost" AS
[Cost_Type], Lbr_Cost AS Cost, Null, OH_Cost
FROM Tran_Mstr_LBR;

--
Build a little, test a little.


AccessKay said:
Duane,

I’m not sure I understand. Are you saying that I can’t do a Union Query?
If so, please see my first post. If that’s not what you’re saying then I’m
really confused because the SQL you sent back looks exactly like the SQL that
I sent. Please forgive me if I'm missing something here.

Duane Hookom said:
You are missing at least one space and the number of fields/columns doesn't
match.

SELECT TransDate, Category, Group, Product, "ODC_Cost" AS [Cost_Type],
ODC_Cost AS Cost, ODC_Cost_Category
FROM Trans_Mstr_ODC
UNION ALL SELECT TransDate, Category, Group, Product, "Labor_Cost" AS
[Cost_Type], Lbr_Cost AS Cost, OH_Cost, Null
FROM Tran_Mstr_LBR;

--
Duane Hookom
Microsoft Access MVP


AccessKay said:
Karl,
I double checked the names and the only changes I made was to the table
name. Exact table name excludes the "tbl".

SELECT TransDate, Category, Group, Product, "ODC_Cost" AS [Cost_Type],
ODC_Cost AS Cost, ODC_Cost_Category
FROM Trans_Mstr_ODC
UNION ALLSELECT TransDate, Category, Group, Product, "Labor_Cost" AS
[Cost_Type], Lbr_Cost AS Cost, OH_Cost, Null
FROM Tran_Mstr_LBR;

Thanks,
Kay



:

Post back your SQL, sometimes copying and pasting post introduces errors.

--
Build a little, test a little.


:

Thanks for your reply.

I read about Union queries but didn’t know that I could use one in this
case. I actually need the Sum of Lbr_Cost and OH_Cost but I guess I could
create another query to add the two and then try the Union again. Am I
correct? But I did receive an Error message when I tested this SQL as
is…â€Invalid SQL statement; expected DELETE, INSERT, PROCEDURE….etc. What am
I missing?


:

Use a union query --
SELECT TransDate, Category, Group, Product, "ODC_Cost" AS [Cost_Type],
ODC_Cost AS Cost, ODC_Cost_Category
FROM tblTrans_Mstr_ODC
UNION ALLSELECT TransDate, Category, Group, Product, "Labor_Cost" AS
[Cost_Type], Lbr_Cost AS Cost, OH_Cost, Null
FROM tblTran_Mstr_Lbr;

--
Build a little, test a little.


:

Good Morning Everyone,

I’m learning and creating a database at the same time. I first added the
table Trans_Mstr_Lbr that is an import from Excel that contains all my data
related to labor. I built queries and reports based on this table. But then
I wanted to add ODC which is another import from Excel. I can’t really
establish a unique relationship between the two tables, though I thought
possibly about the date field. They both have the same month/year in common.
My goal is to create a query and report that displays the Labor Cost,ODC
Cost, and then the sum of the two, grouped by Category, Group, and Product.
Since these tables have such similar fields, I thought maybe I should combine
them??? Though, I’d rather keep them separately. I played around with
trying to create some queries and adding common tables and joins…and then
decided that I was getting nowhere and thought I’d ask the experts. What
should I do in this case? Please feel free to elaborate and you’re help is
greatly appreciated.

tblTran_Mstr_Lbr
ID (auto#)
TransDate
Category
Group
Product
Lbr_Cost
OH_Cost

tblTrans_Mstr_ODC
ID (auto#)
TransDate
Category
Group
Product
ODC_Cost_Category
ODC_Cost

Thanks,
Kay
 
Ad

Advertisements

A

AccessKay

Yes, this worked Karl…I’m very pleased. But there is this little calendar
icon by the TransDate in the Dataview screen. Is this normal?


KARL DEWEY said:
I had one more field in the second part than the first. Try it now --
SELECT TransDate, Category, Group, Product, "ODC_Cost" AS [Cost_Type],
ODC_Cost AS Cost, ODC_Cost_Category, Null AS OH_Cost
FROM Trans_Mstr_ODC
UNION ALL SELECT TransDate, Category, Group, Product, "Labor_Cost" AS
[Cost_Type], Lbr_Cost AS Cost, Null, OH_Cost
FROM Tran_Mstr_LBR;

--
Build a little, test a little.


AccessKay said:
Duane,

I’m not sure I understand. Are you saying that I can’t do a Union Query?
If so, please see my first post. If that’s not what you’re saying then I’m
really confused because the SQL you sent back looks exactly like the SQL that
I sent. Please forgive me if I'm missing something here.

Duane Hookom said:
You are missing at least one space and the number of fields/columns doesn't
match.

SELECT TransDate, Category, Group, Product, "ODC_Cost" AS [Cost_Type],
ODC_Cost AS Cost, ODC_Cost_Category
FROM Trans_Mstr_ODC
UNION ALL SELECT TransDate, Category, Group, Product, "Labor_Cost" AS
[Cost_Type], Lbr_Cost AS Cost, OH_Cost, Null
FROM Tran_Mstr_LBR;

--
Duane Hookom
Microsoft Access MVP


:

Karl,
I double checked the names and the only changes I made was to the table
name. Exact table name excludes the "tbl".

SELECT TransDate, Category, Group, Product, "ODC_Cost" AS [Cost_Type],
ODC_Cost AS Cost, ODC_Cost_Category
FROM Trans_Mstr_ODC
UNION ALLSELECT TransDate, Category, Group, Product, "Labor_Cost" AS
[Cost_Type], Lbr_Cost AS Cost, OH_Cost, Null
FROM Tran_Mstr_LBR;

Thanks,
Kay



:

Post back your SQL, sometimes copying and pasting post introduces errors.

--
Build a little, test a little.


:

Thanks for your reply.

I read about Union queries but didn’t know that I could use one in this
case. I actually need the Sum of Lbr_Cost and OH_Cost but I guess I could
create another query to add the two and then try the Union again. Am I
correct? But I did receive an Error message when I tested this SQL as
is…â€Invalid SQL statement; expected DELETE, INSERT, PROCEDURE….etc. What am
I missing?


:

Use a union query --
SELECT TransDate, Category, Group, Product, "ODC_Cost" AS [Cost_Type],
ODC_Cost AS Cost, ODC_Cost_Category
FROM tblTrans_Mstr_ODC
UNION ALLSELECT TransDate, Category, Group, Product, "Labor_Cost" AS
[Cost_Type], Lbr_Cost AS Cost, OH_Cost, Null
FROM tblTran_Mstr_Lbr;

--
Build a little, test a little.


:

Good Morning Everyone,

I’m learning and creating a database at the same time. I first added the
table Trans_Mstr_Lbr that is an import from Excel that contains all my data
related to labor. I built queries and reports based on this table. But then
I wanted to add ODC which is another import from Excel. I can’t really
establish a unique relationship between the two tables, though I thought
possibly about the date field. They both have the same month/year in common.
My goal is to create a query and report that displays the Labor Cost,ODC
Cost, and then the sum of the two, grouped by Category, Group, and Product.
Since these tables have such similar fields, I thought maybe I should combine
them??? Though, I’d rather keep them separately. I played around with
trying to create some queries and adding common tables and joins…and then
decided that I was getting nowhere and thought I’d ask the experts. What
should I do in this case? Please feel free to elaborate and you’re help is
greatly appreciated.

tblTran_Mstr_Lbr
ID (auto#)
TransDate
Category
Group
Product
Lbr_Cost
OH_Cost

tblTrans_Mstr_ODC
ID (auto#)
TransDate
Category
Group
Product
ODC_Cost_Category
ODC_Cost

Thanks,
Kay
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top