RelationShips Queries and joins
in [Queries]
|
From: Merge on 11 May 2010 18:01 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?
From: KARL DEWEY on 11 May 2010 19:22 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? > >
From: John W. Vinson on 11 May 2010 20:24 On Tue, 11 May 2010 15:01:01 -0700, Merge <Merge(a)discussions.microsoft.com> 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]
From: Merge on 11 May 2010 21:57 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? > > > >
From: Merge on 11 May 2010 22:08
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 <Merge(a)discussions.microsoft.com> > 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] > . > |