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

Select Query, joined field in 1 table has wildcard characters

P: 4
I'm running Access 2000 on Windows XP.

I have a simple select query, with two tables joined at the "Account" field. The account field has 5 characters of text in both tables

Table A is a reference table with categories for the accounts.
Table B is the main transactions table with actual activity by account.

Table A's records have the account field utilizing wildcard characters, ex. "111##", to capture ranges of accounts.
Table B's records have the account field with the full 5 characters, ie. "11123".

My query returns no results.

I didn't want to have individual records for each and every account in Table A, which is why I used the wildcard characters. Is this not possible, or should I be using another approach?



Any help will be greatly appreciated.
Thanks!
Nov 13 '06 #1
Share this Question
Share on Google+
9 Replies


100+
P: 1,646
I'm running Access 2000 on Windows XP.

I have a simple select query, with two tables joined at the "Account" field. The account field has 5 characters of text in both tables

Table A is a reference table with categories for the accounts.
Table B is the main transactions table with actual activity by account.

Table A's records have the account field utilizing wildcard characters, ex. "111##", to capture ranges of accounts.
Table B's records have the account field with the full 5 characters, ie. "11123".

My query returns no results.

I didn't want to have individual records for each and every account in Table A, which is why I used the wildcard characters. Is this not possible, or should I be using another approach?



Any help will be greatly appreciated.
Thanks!
Hi. Table A is a lookup table which should have a UID (unique identifier) which will normally be the autonumber set by the database. This will be the primary key (PK) for this table. The next field will be the data which is your account info.

Table B will have a foreign key (FK) which you will link to the UID of table A

Would you please submit the query that does not work?
Thanks
Nov 13 '06 #2

P: 4
Hi. Table A is a lookup table which should have a UID (unique identifier) which will normally be the autonumber set by the database. This will be the primary key (PK) for this table. The next field will be the data which is your account info.

Table B will have a foreign key (FK) which you will link to the UID of table A

Would you please submit the query that does not work?
Thanks

Here's the sql view of my query, although I'm using the design-view to construct the query.

SELECT [Table A].descp0, Sum([Table B].[trans amt]) AS [SumOftrans amt]
FROM [Table B] LEFT JOIN [Table A] ON [Table B].Account = [Table A].Account
GROUP BY [Table A].descp0;

Not sure how I'd utilize your suggested approach with the FK?
Nov 13 '06 #3

NeoPa
Expert Mod 15k+
P: 31,485
I often recommend using the design approach to develop a query.
Unfortunately, that's not very good for communicating the details of that query.
Or should I rather say that, fortunately, the SQL view is!
Nov 13 '06 #4

MMcCarthy
Expert Mod 10K+
P: 14,534
I'm running Access 2000 on Windows XP.

I have a simple select query, with two tables joined at the "Account" field. The account field has 5 characters of text in both tables

Table A is a reference table with categories for the accounts.
Table B is the main transactions table with actual activity by account.

Table A's records have the account field utilizing wildcard characters, ex. "111##", to capture ranges of accounts.
Table B's records have the account field with the full 5 characters, ie. "11123".

My query returns no results.
You can't join two tables on a field that is not the same. In other words you cannot join on a like statement.


I didn't want to have individual records for each and every account in Table A, which is why I used the wildcard characters. Is this not possible, or should I be using another approach?
Create another table with a list of all accounts and a foreign key reference to the primary key of Table A. Then remove the field from Table A altogether.

You can now join table A and Table B together using the new table.

Table A will join to new table on the PK to FK reference and Table B will join to the new table on the Account ref.
Nov 13 '06 #5

P: 4
thanks for your help, I'll give it a try
Nov 16 '06 #6

MMcCarthy
Expert Mod 10K+
P: 14,534
thanks for your help, I'll give it a try
You're welcome.

If you've any questions, please let us know and check out the 'Links to useful Sites' thread at the top (or nearly) of the Access Forum.
Nov 16 '06 #7

nico5038
Expert 2.5K+
P: 3,072
Hi Romanko,

You have a design issue at hand. Can you explain why you use "grouped" accounts ?

Normally you would record different information for a higher level of account numbers. This can be modelled best by having a "tblSuperAccount" with the overall information. Then you can relate the "tblSubAccount" (or e.g. "tblDetailAccount) by the unique key of the tblSuperAccount.
Using "fixed" ranges is normally regarded as asking for trouble. Just imagine that the DetailAccount grows to a number above 999....

Why do you want to suppress data for all accounts ?

Nic;o)
Nov 16 '06 #8

P: 4
I took the fixed ranges approach because there are something like 90,000 account numbers (they are accounting expense accounts). I need to keep them categorised, but new accounts are added and old ones deleted regularly, however the numbering always follow set rules: ex. accounts 31000 to 31999 are categorized as office supplies. So I just wanted to setup one record in the table with the account field set at 31### to capture this range, reducing how much time I'd spend maintaining this table, and the size of the table.
Nov 17 '06 #9

nico5038
Expert 2.5K+
P: 3,072
OK, then the "normalized" way is to have a tblCategory for the fields needed on this "level".
In the tblAccount you should use 2 fields:
1) CategoryID
2) AccountSequenceNo
Thus you can relate the tables easily in a JOIN and for showing the needed full account number use:
Select CategoryID & AccountSequenceNo as AccountNo, ...

Getting the idea ?

Nic;o)
Nov 17 '06 #10

Post your reply

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