Select Query, joined field in 1 table has wildcard characters

[romanko]

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!

[willakawill]

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

[romanko]

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?

[NeoPa]

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!

[MMcCarthy]

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.

[romanko]

thanks for your help, I'll give it a try

[MMcCarthy]

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.

[nico5038]

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)

[romanko]

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.

[nico5038]

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)