Use first 3 characters in query

[Ben]

Hi - I have two tables - BAL_ACT and APS. In the BAL_ACT table, there is an
Acct_nbr field that is 9 characters. In my query, I want to "join" the first
3 characters of Acct_nbr with the 3-character field Prefix in the APS table.
I'm not sure how to do that. I build all my queries in Design view as I
haven't been "trained" using SQL view.

Is there an easy way to do this?

Thanks! Ben

 

[golfinray]

In a new column in design view, Whatever:Left([yourfield],3)

 

[John Spencer MVP]

One EASY way would be to build and save a query of Bal_Act that has a
calculated field returning the first 3 characters of Acct_nbr and then use
that query to join to the APS table.

Query One (named qBal_ActPrefix):
Add your table to the query
Add all the fields to the grid
In a BLANK field box type
ActPrefix: Left(Acct_Nbr,3)
Save the query

Now, open a new query
Add the saved query you have just created and the APS table
Join Aps table Prefix field to the query's Prefix field.

If you want to have ONE query that does this then you will need to work in the
SQL view OR you can use a cartesian join of the two tables. Cartesian joins
tend to be SLOW if you have a large number of records. To do so:

Add both tables to the query design view
Don't set up a join
Under Acct_Nbr enter the criteria
CRITERIA: LIKE [APS].[Prefix] & "*"

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Ben]

Thank you very much! I did the two-step query. Just for my own education,
what would my code be in SQL view to perform this in one query?

Thanks again,
Ben

One EASY way would be to build and save a query of Bal_Act that has a
calculated field returning the first 3 characters of Acct_nbr and then use
that query to join to the APS table.

Query One (named qBal_ActPrefix):
Add your table to the query
Add all the fields to the grid
In a BLANK field box type
ActPrefix: Left(Acct_Nbr,3)
Save the query

Now, open a new query
Add the saved query you have just created and the APS table
Join Aps table Prefix field to the query's Prefix field.

If you want to have ONE query that does this then you will need to work in the
SQL view OR you can use a cartesian join of the two tables. Cartesian joins
tend to be SLOW if you have a large number of records. To do so:

Add both tables to the query design view
Don't set up a join
Under Acct_Nbr enter the criteria
CRITERIA: LIKE [APS].[Prefix] & "*"

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Hi - I have two tables - BAL_ACT and APS. In the BAL_ACT table, there is an
Acct_nbr field that is 9 characters. In my query, I want to "join" the first
3 characters of Acct_nbr with the 3-character field Prefix in the APS table.
I'm not sure how to do that. I build all my queries in Design view as I
haven't been "trained" using SQL view.

Is there an easy way to do this?

Thanks! Ben

 

[John Spencer MVP]

Post your two queries and I will show you. One query solution only works if
your field and table names consist of only letters, numbers, and the
underscore character - no spaces allowed.

Anyway in the SQL view you would see something like the following

SELECT BAL_Act.*
FROM BAL_Act INNER JOIN APS
ON BAL_Act.Acct_nbr LIKE APS.Prefix & "*"

You can add specific items to the SELECT clause. If you are lazy, you can
build most of this query using the query grid.

-- Add both tables
-- Add the fields you want to see
-- Make a join between Acct_Nbr and Prefix fields
-- Switch to SQL view (View: SQL)
-- Edit the ON clause which should read
BAL_Act.Acct_nbr =APS.Prefix
to read
BAL_Act.Acct_nbr LIKE APS.Prefix & "*"


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Thank you very much! I did the two-step query. Just for my own education,
what would my code be in SQL view to perform this in one query?

Thanks again,
Ben

One EASY way would be to build and save a query of Bal_Act that has a
calculated field returning the first 3 characters of Acct_nbr and then use
that query to join to the APS table.

Query One (named qBal_ActPrefix):
Add your table to the query
Add all the fields to the grid
In a BLANK field box type
ActPrefix: Left(Acct_Nbr,3)
Save the query

Now, open a new query
Add the saved query you have just created and the APS table
Join Aps table Prefix field to the query's Prefix field.

If you want to have ONE query that does this then you will need to work in the
SQL view OR you can use a cartesian join of the two tables. Cartesian joins
tend to be SLOW if you have a large number of records. To do so:

Add both tables to the query design view
Don't set up a join
Under Acct_Nbr enter the criteria
CRITERIA: LIKE [APS].[Prefix] & "*"

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Hi - I have two tables - BAL_ACT and APS. In the BAL_ACT table, there is an
Acct_nbr field that is 9 characters. In my query, I want to "join" the first
3 characters of Acct_nbr with the 3-character field Prefix in the APS table.
I'm not sure how to do that. I build all my queries in Design view as I
haven't been "trained" using SQL view.

Is there an easy way to do this?

Thanks! Ben