By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
431,852 Members | 2,152 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 431,852 IT Pros & Developers. It's quick & easy.

Format Records from a Query

LacrosseB0ss
100+
P: 113
hello all;

just wondering if this can be done. If so, I am looking for how to do it. I have a small Access macro that links 2 tables based on an address then assigns the address an ID based on the 2nd table's records. The address is taken from an imported Excel sheet, this funtionailty works like a charm.

What I would like to do is say "Select * From Tbl1 Inner Join qry1 ON Tbl1 (Tbl1.Address = qry1.Address)". Now, this also works ok. What we have discovered is that in the main database most of the street types are short forms (rd for road, etc). If the user entered "ROAD" it would not match. We created an Excel macro a few months back to change all records to short form which also works.

Now what we've found is that some of the database records are long form (Road, Avenue, etc). We do not have control over the database so we can not change these to short form. We would like to use the LIKE statement in a query to help with this.

Is it possible to say "Select * From Tbl2 Where Address Like "(record up to 2nd space)*"; ?

All addresses are in the format ##_STREET_TYPE where the underscores are spaces. Any help would be greatly appreciated.

-LB
May 17 '07 #1
Share this Question
Share on Google+
4 Replies


NeoPa
Expert Mod 15k+
P: 31,470
I'm sorry LB but you're heading for trouble here (I guess you may suspect that already).
I've done something similar with company names (because I had to and not because it's a good idea). I worked on the principal of removing abiguous items (PLC; Inc; & son; and son; ...), then using the residual text as a key. This works in a limited way, but there are so many potential problems involved even in such a simplified process as that. To do something similar with an address field would be less reliable even than that.
Personally I would attempt to explain to the requestor how much of a good idea this isn't.
Good luck.
May 19 '07 #2

ADezii
Expert 5K+
P: 8,628
hello all;

just wondering if this can be done. If so, I am looking for how to do it. I have a small Access macro that links 2 tables based on an address then assigns the address an ID based on the 2nd table's records. The address is taken from an imported Excel sheet, this funtionailty works like a charm.

What I would like to do is say "Select * From Tbl1 Inner Join qry1 ON Tbl1 (Tbl1.Address = qry1.Address)". Now, this also works ok. What we have discovered is that in the main database most of the street types are short forms (rd for road, etc). If the user entered "ROAD" it would not match. We created an Excel macro a few months back to change all records to short form which also works.

Now what we've found is that some of the database records are long form (Road, Avenue, etc). We do not have control over the database so we can not change these to short form. We would like to use the LIKE statement in a query to help with this.

Is it possible to say "Select * From Tbl2 Where Address Like "(record up to 2nd space)*"; ?

All addresses are in the format ##_STREET_TYPE where the underscores are spaces. Any help would be greatly appreciated.

-LB
I totally agree with NeoPa on this one - avoid this approach like the plague!
May 19 '07 #3

LacrosseB0ss
100+
P: 113
Our team has set up a meeting with the requestor for later this week to ask if just running our previously created macro is still fine. I also did not really want to delve into this territory unless I had to. Someone brought it up at a meeting and I, being the co-op student, was presented with the daunting task.

Thanks for the input folks.
May 22 '07 #4

NeoPa
Expert Mod 15k+
P: 31,470
No problem LB.
You can always quote us if that helps in the meeting. We don't say this lightly and are confident of this advice :)
May 22 '07 #5

Post your reply

Sign in to post your reply or Sign up for a free account.