RelationShips Queries and joins

Not sure where to start and the posts I have read I do not fully understand

I created 2 tables (remitt and Billing) and for each table I created a
query--Because I needed to create a commmon expression (1st 10 digits of a
name & a w/e date & Pay code). The names never match 100%

I want to make a final query and bring over the Billing info into the remitt
and create a final query

Can I do this?

Post the SQL of the two queries you have now by opening in design view, click
on VIEW - SQL View, highlight all in the window that opens, copy, and paste
in a post.

--
Build a little, test a little.


"Merge" wrote:

Not sure where to start and the posts I have read I do not fully understand

I created 2 tables (remitt and Billing) and for each table I created a
query--Because I needed to create a commmon expression (1st 10 digits of a
name & a w/e date & Pay code). The names never match 100%

I want to make a final query and bring over the Billing info into the remitt
and create a final query

Can I do this?

On Tue, 11 May 2010 15:01:01 -0700, Merge
wrote:

Not sure where to start and the posts I have read I do not fully understand

I created 2 tables (remitt and Billing) and for each table I created a
query--Because I needed to create a commmon expression (1st 10 digits of a
name & a w/e date & Pay code). The names never match 100%

I want to make a final query and bring over the Billing info into the remitt
and create a final query

Can I do this?


Only if you can uniquely identify the records in both tables. Why do you feel
that you need to concatenate? What are these names - people's names? If so,
you can't be sure that they're unique OR that they'll be entered consistantly.

Could you post an example of the data and what you're trying to use for
joining?
--

John W. Vinson [MVP]

SELECT [IQN Remitt].[Invoice Number], Left([Current Contractor Full Name],10)
& [Date - Week Ending Date] & IIf([Hours - Regular Hours Worked]0,"Regular
Hours",IIf([Hours - Overtime Hours Worked]0,"Overtime 1","DT")) AS [Name WE
Pay Code], Left([Current Contractor Full Name],10) & [Date - Week Ending
Date] AS [Name WE], [IQN Remitt].[Current Contractor Full Name], [IQN
Remitt].[Hours - Regular Hours Worked], [IQN Remitt].[Hours - Overtime Hours
Worked], [IQN Remitt].[Hours - Double Time Hours Worked], [IQN Remitt].[Rate
- Reimbursement Rate], [IQN Remitt].[Rate - OT Reimbursement Rate], [IQN
Remitt].[Rate - DT Reimbursement Rate], [IQN Remitt].[Expected Reimbursement
Amount], [IQN Remitt].[Supplier Check #], [IQN Remitt].[Date - Week Ending
Date], [IQN Remitt].[Date - Supplier Check Date]
FROM [IQN Remitt];


SELECT Left([Employee],10) & [Week End] & [PayCode] AS [Name WE and Pay
Code], Left([Employee],10) & [Week End] AS [Name and WE],
TRU_Billing.Employee, TRU_Billing.Field2, TRU_Billing.[RMX ID],
TRU_Billing.[SS ID], TRU_Billing.[Client Name], TRU_Billing.[Inv #],
TRU_Billing.Date, TRU_Billing.[Week End], TRU_Billing.PayCode,
TRU_Billing.[Line Desc], TRU_Billing.units, TRU_Billing.Rate,
TRU_Billing.[Line $], TRU_Billing.Discount, TRU_Billing.[Sales Tax],
TRU_Billing.Net
FROM TRU_Billing, [IQN Remitt Query];


"KARL DEWEY" wrote:

Post the SQL of the two queries you have now by opening in design view, click
on VIEW - SQL View, highlight all in the window that opens, copy, and paste
in a post.

--
Build a little, test a little.


"Merge" wrote:

Not sure where to start and the posts I have read I do not fully understand

I created 2 tables (remitt and Billing) and for each table I created a
query--Because I needed to create a commmon expression (1st 10 digits of a
name & a w/e date & Pay code). The names never match 100%

I want to make a final query and bring over the Billing info into the remitt
and create a final query

Can I do this?

the 3 combination will create a unique record. Do I need to do this
differnetly? I am taking the name and using the 1st 10 digits to make it
match better.

"John W. Vinson" wrote:

On Tue, 11 May 2010 15:01:01 -0700, Merge
wrote:

Not sure where to start and the posts I have read I do not fully understand

I created 2 tables (remitt and Billing) and for each table I created a
query--Because I needed to create a commmon expression (1st 10 digits of a
name & a w/e date & Pay code). The names never match 100%

I want to make a final query and bring over the Billing info into the remitt
and create a final query

Can I do this?


Only if you can uniquely identify the records in both tables. Why do you feel
that you need to concatenate? What are these names - people's names? If so,
you can't be sure that they're unique OR that they'll be entered consistantly.

Could you post an example of the data and what you're trying to use for
joining?
--

John W. Vinson [MVP]
.

You have a 'spreadsheet' and not a very good one instead of a 'normalized'
relational database tables.

With text data fields like [Date - Week Ending Date] and [Date - Supplier
Check Date] you will never be able to sort the records. You will not be able
to pull records for any given period of time.

You need to revise your table structure.

--
Build a little, test a little.


"Merge" wrote:

SELECT [IQN Remitt].[Invoice Number], Left([Current Contractor Full Name],10)
& [Date - Week Ending Date] & IIf([Hours - Regular Hours Worked]0,"Regular
Hours",IIf([Hours - Overtime Hours Worked]0,"Overtime 1","DT")) AS [Name WE
Pay Code], Left([Current Contractor Full Name],10) & [Date - Week Ending
Date] AS [Name WE], [IQN Remitt].[Current Contractor Full Name], [IQN
Remitt].[Hours - Regular Hours Worked], [IQN Remitt].[Hours - Overtime Hours
Worked], [IQN Remitt].[Hours - Double Time Hours Worked], [IQN Remitt].[Rate
- Reimbursement Rate], [IQN Remitt].[Rate - OT Reimbursement Rate], [IQN
Remitt].[Rate - DT Reimbursement Rate], [IQN Remitt].[Expected Reimbursement
Amount], [IQN Remitt].[Supplier Check #], [IQN Remitt].[Date - Week Ending
Date], [IQN Remitt].[Date - Supplier Check Date]
FROM [IQN Remitt];


SELECT Left([Employee],10) & [Week End] & [PayCode] AS [Name WE and Pay
Code], Left([Employee],10) & [Week End] AS [Name and WE],
TRU_Billing.Employee, TRU_Billing.Field2, TRU_Billing.[RMX ID],
TRU_Billing.[SS ID], TRU_Billing.[Client Name], TRU_Billing.[Inv #],
TRU_Billing.Date, TRU_Billing.[Week End], TRU_Billing.PayCode,
TRU_Billing.[Line Desc], TRU_Billing.units, TRU_Billing.Rate,
TRU_Billing.[Line $], TRU_Billing.Discount, TRU_Billing.[Sales Tax],
TRU_Billing.Net
FROM TRU_Billing, [IQN Remitt Query];


"KARL DEWEY" wrote:

Post the SQL of the two queries you have now by opening in design view, click
on VIEW - SQL View, highlight all in the window that opens, copy, and paste
in a post.

--
Build a little, test a little.


"Merge" wrote:

Not sure where to start and the posts I have read I do not fully understand

I created 2 tables (remitt and Billing) and for each table I created a
query--Because I needed to create a commmon expression (1st 10 digits of a
name & a w/e date & Pay code). The names never match 100%

I want to make a final query and bring over the Billing info into the remitt
and create a final query

Can I do this?

On Tue, 11 May 2010 19:08:11 -0700, Merge
wrote:

the 3 combination will create a unique record. Do I need to do this
differnetly? I am taking the name and using the 1st 10 digits to make it
match better.

Names don't have digits (well, R2D2 does), they have characters; and using the
first 10 characters of a 21 character name will make it LESS unique, not more
unique. And if these are people's names, truncating "Bill Jones" and "William
Jones" won't help make them match!

You do NOT need to create a new concatenated field to match records. You can
join two tables on one field, or on three fields, or on ten fields if you
wish.

Again: could you post an example (obfuscated for privacy if need be) of your
data, and explain what constitutes a match? Do you have a Primary Key in both
tables? If so what is that key?
--

John W. Vinson [MVP]

Thanks that is true

I will fix up the column names, I just imported the data from a report I ran.

"KARL DEWEY" wrote:

You have a 'spreadsheet' and not a very good one instead of a 'normalized'
relational database tables.

With text data fields like [Date - Week Ending Date] and [Date - Supplier
Check Date] you will never be able to sort the records. You will not be able
to pull records for any given period of time.

You need to revise your table structure.

--
Build a little, test a little.


"Merge" wrote:

SELECT [IQN Remitt].[Invoice Number], Left([Current Contractor Full Name],10)
& [Date - Week Ending Date] & IIf([Hours - Regular Hours Worked]0,"Regular
Hours",IIf([Hours - Overtime Hours Worked]0,"Overtime 1","DT")) AS [Name WE
Pay Code], Left([Current Contractor Full Name],10) & [Date - Week Ending
Date] AS [Name WE], [IQN Remitt].[Current Contractor Full Name], [IQN
Remitt].[Hours - Regular Hours Worked], [IQN Remitt].[Hours - Overtime Hours
Worked], [IQN Remitt].[Hours - Double Time Hours Worked], [IQN Remitt].[Rate
- Reimbursement Rate], [IQN Remitt].[Rate - OT Reimbursement Rate], [IQN
Remitt].[Rate - DT Reimbursement Rate], [IQN Remitt].[Expected Reimbursement
Amount], [IQN Remitt].[Supplier Check #], [IQN Remitt].[Date - Week Ending
Date], [IQN Remitt].[Date - Supplier Check Date]
FROM [IQN Remitt];


SELECT Left([Employee],10) & [Week End] & [PayCode] AS [Name WE and Pay
Code], Left([Employee],10) & [Week End] AS [Name and WE],
TRU_Billing.Employee, TRU_Billing.Field2, TRU_Billing.[RMX ID],
TRU_Billing.[SS ID], TRU_Billing.[Client Name], TRU_Billing.[Inv #],
TRU_Billing.Date, TRU_Billing.[Week End], TRU_Billing.PayCode,
TRU_Billing.[Line Desc], TRU_Billing.units, TRU_Billing.Rate,
TRU_Billing.[Line $], TRU_Billing.Discount, TRU_Billing.[Sales Tax],
TRU_Billing.Net
FROM TRU_Billing, [IQN Remitt Query];


"KARL DEWEY" wrote:

Post the SQL of the two queries you have now by opening in design view, click
on VIEW - SQL View, highlight all in the window that opens, copy, and paste
in a post.

--
Build a little, test a little.


"Merge" wrote:

Not sure where to start and the posts I have read I do not fully understand

I created 2 tables (remitt and Billing) and for each table I created a
query--Because I needed to create a commmon expression (1st 10 digits of a
name & a w/e date & Pay code). The names never match 100%

I want to make a final query and bring over the Billing info into the remitt
and create a final query

Can I do this?

My problem is the data format is different on both reports
Doing this concatenate The pay code on one reports references the pay code
and on the other report you have to figure out the code by what type of hours
they have worked. then for the nameJohnson Dominick is on one and Johnson
Dominick Jr is on another this is why I take the first 10 digits

I want to keep it simple I may just export the query to excel and import it
back as a table. I do not have much support from my IT group so I am tackling
this on my own (Novice user)

I am just using this to match Data to create coding for payments received.

"John W. Vinson" wrote:

On Tue, 11 May 2010 19:08:11 -0700, Merge
wrote:

the 3 combination will create a unique record. Do I need to do this
differnetly? I am taking the name and using the 1st 10 digits to make it
match better.

Names don't have digits (well, R2D2 does), they have characters; and using the
first 10 characters of a 21 character name will make it LESS unique, not more
unique. And if these are people's names, truncating "Bill Jones" and "William
Jones" won't help make them match!

You do NOT need to create a new concatenated field to match records. You can
join two tables on one field, or on three fields, or on ten fields if you
wish.

Again: could you post an example (obfuscated for privacy if need be) of your
data, and explain what constitutes a match? Do you have a Primary Key in both
tables? If so what is that key?
--

John W. Vinson [MVP]
.

One way would be to build a translation table like this --
tblTranslate --
EmpID - Primary key
IQN Remitt - text - names used in the table
TRU_Billing - text - names used in the table

Then you can join the tables using tblTranslate table.

--
Build a little, test a little.


"Merge" wrote:

My problem is the data format is different on both reports
Doing this concatenate The pay code on one reports references the pay code
and on the other report you have to figure out the code by what type of hours
they have worked. then for the nameJohnson Dominick is on one and Johnson
Dominick Jr is on another this is why I take the first 10 digits

I want to keep it simple I may just export the query to excel and import it
back as a table. I do not have much support from my IT group so I am tackling
this on my own (Novice user)

I am just using this to match Data to create coding for payments received.

"John W. Vinson" wrote:

On Tue, 11 May 2010 19:08:11 -0700, Merge
wrote:

the 3 combination will create a unique record. Do I need to do this
differnetly? I am taking the name and using the 1st 10 digits to make it
match better.

Names don't have digits (well, R2D2 does), they have characters; and using the
first 10 characters of a 21 character name will make it LESS unique, not more
unique. And if these are people's names, truncating "Bill Jones" and "William
Jones" won't help make them match!

You do NOT need to create a new concatenated field to match records. You can
join two tables on one field, or on three fields, or on ten fields if you
wish.

Again: could you post an example (obfuscated for privacy if need be) of your
data, and explain what constitutes a match? Do you have a Primary Key in both
tables? If so what is that key?
--

John W. Vinson [MVP]
.