RelationShips Queries and joins

[Merge]

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?

 

[KARL DEWEY]

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.

 

[John W. Vinson]

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?

 

[Merge]

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

 

[Merge]

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.

 

[KARL DEWEY]

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.

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

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.

 

[John W. Vinson]

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?

 

[Merge]

Thanks that is true

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

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.

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

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.


:

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?

 

[Merge]

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.

 

[KARL DEWEY]

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.

 

[John W. Vinson]

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.

You're unfortunately making it more complicated, not simpler!

Don't confuse data STORAGE with data DISPLAY. You should not - cannot! - let
the desired format of a report drive your table or database structure. YOu
should have a table of People, with a unique PersonID, so that Dominick
Johnson is in a table with a structure like:

PersonID 312
FirstName "Dominick"
LastName "Johnson"
Suffix "Jr."
Nickname "Dom"


which can be displayed on a report any way you like.

This structure would also allow you to accommodate the case that you might be
dealing with Dom Sr. and Dom Jr., different people doing different things.

You might want to look at some of these resources:

Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

Roger Carlson's tutorials, samples and tips:
http://www.rogersaccesslibrary.com/

A free tutorial written by Crystal:
http://allenbrowne.com/casu-22.html

A video how-to series by Crystal:
http://www.YouTube.com/user/LearnAccessByCrystal

MVP Allen Browne's tutorials:
http://allenbrowne.com/links.html#Tutorials

Crystal's and Allen's tutorials in particular might give you a good
foundation.