I have two tables. One which has a list of redeemed discount codes and discount amounts. The other with the discount code prefix as well as an associated a GL. I would like to group redeemed discount codes by SUM and GL but my issue is that discount codes can have random trailing characters (ie, CODE98392 with CODE being the prefix).

My thought here would be that I would need to create a join which utilizes a wildcard to just find a portion of the code. I created a sample db, the output in my query (Query1) should resemble:

DEMO -$55.00 - 123555555
DISC - $35.00 - 555666999
TEST - $60.00 - 12345678

Your help on this is appreciated.

Thank you,Click to view attachment

sounds like this table is not normalized. from your description, it seems that discount codes are comprised of two values - a prefix, and random trailing characters. the two pieces of data should be stored in two separate fields in the table, not concatenated into one value for storage.

if you split the discount code field into its' atomic values, so that you have a prefix field, and a "suffix" field, you'll find it easy to link your two tables in a query, on the common prefix field. then it should be easy to pull the associated GL into the query grid, and create a Totals query to group the records as you wish.

hth
tina

Beings there are a mixed amount of discount codes with mixed character lengths, normalizing the data (breaking out the prefix and suffix) will take a larger amount of time and negate the benefit of improving this process.

Per my original request, is there a way to match based on a wildcard?

Yes, a non-equi join:

SELECT <whatever>
FROM tblCodes INNER JOIN tblGL
ON tblGL.Longcode LIKE tblCodes.ShortCode & "*"

But I absolutely agree that your best bet will be to have the fields split out. For one thing, the above query will give false matches in some cases - suppose you had two discount codes DI and DISC, and records in your table with DI12243 and DISC3512. They will BOTH match the DI short code.

That is perfect, thank you. I think we have distinct enough prefixes that it should not effect things.