If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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? |
#2
|
|||
|
|||
RelationShips Queries and joins
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? |
#3
|
|||
|
|||
RelationShips Queries and joins
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] |
#4
|
|||
|
|||
RelationShips Queries and joins
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? |
#5
|
|||
|
|||
RelationShips Queries and joins
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] . |
#6
|
|||
|
|||
RelationShips Queries and joins
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? |
#7
|
|||
|
|||
RelationShips Queries and joins
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] |
#8
|
|||
|
|||
RelationShips Queries and joins
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? |
#9
|
|||
|
|||
RelationShips Queries and joins
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] . |
#10
|
|||
|
|||
RelationShips Queries and joins
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] . |
|
Thread Tools | |
Display Modes | |
|
|