Prev: Join on a calculated field
Next: Crosstab column data count
From: KARL DEWEY on 12 May 2010 01:05
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?
> > >
> > >
From: John W. Vinson on 12 May 2010 01:19
On Tue, 11 May 2010 19:08:11 -0700, Merge <Merge(a)discussions.microsoft.com>
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]
From: Merge on 12 May 2010 05:28
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?
> > > >
> > > >
From: Merge on 12 May 2010 06:55
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 <Merge(a)discussions.microsoft.com>
> 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]
> .
>
From: KARL DEWEY on 12 May 2010 11:34
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 <Merge(a)discussions.microsoft.com>
> > 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]
> > .
> >
First  |  Prev  |  Next  |  Last
Pages: 1 2 3
Prev: Join on a calculated field
Next: Crosstab column data count