Linking a Number and Text field

[Gary Dolliver]

Hi all,
I have two tables (Entries and Payments) on which I am trying to compare
vouchers redeemed. I am needing to link [Voucher_Num] from table Entries,
which is an AutoNumber field to [Number] in table Payments which is a text
field (I need this to be a text field as this payment table contains all
payment types and I need to be able to capture either a CC number, check #,
money order number... and many of them begin with 0 or have alpha
characters). I am wanting to find out if there are any vouchers that have
redeemed (determined by fields in table Entries) that are NOT in table
payments (after running other queries, it appears there are based on payment
amounts not matching).
I thought I would need to use a query to manipulate the data in one table
first and then do the comparison, but I have not been able to get it to work.
Any help would be greatly appreciated, thanks!
-gary

 

[kingston via AccessMonster.com]

Create a query on the table Payments with a calculated field that converts
[Number] to a long integer:

LinkID: CLng([Number])

Then create another query using the first query and the table Entries. Join

Hi all,
I have two tables (Entries and Payments) on which I am trying to compare
vouchers redeemed. I am needing to link [Voucher_Num] from table Entries,
which is an AutoNumber field to [Number] in table Payments which is a text
field (I need this to be a text field as this payment table contains all
payment types and I need to be able to capture either a CC number, check #,
money order number... and many of them begin with 0 or have alpha
characters). I am wanting to find out if there are any vouchers that have
redeemed (determined by fields in table Entries) that are NOT in table
payments (after running other queries, it appears there are based on payment
amounts not matching).
I thought I would need to use a query to manipulate the data in one table
first and then do the comparison, but I have not been able to get it to work.
Any help would be greatly appreciated, thanks!
-gary

 

[Jeff Boyce]

Gary

So, you are saying that you have an "ID" field in tblPayments that does NOT
match the corresponding field in tblEntries. How do you expect Access to
tell the that records from each are related? How would you explain the
relationship to a coworker you were asking to do the task 'manually'?

It sounds like your tblPayments field stores more than one fact. This is
considered a poor database design, and for the reasons you've described. Is
there a way you could add more fields and keep "one fact in one field"?

Regards

Jeff Boyce
Microsoft Office/Access MVP