Query set-up with two tables

[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

 

[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;

 

[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?

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

 

[KARL DEWEY]

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

 

[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

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

 

[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

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

 

[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.

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

 

[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.

 

[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.

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.

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

 

[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?

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.

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.

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