Assigning Duplicate Values

[Gkann]

Help - I am a long time lurker and first time poster.

Here is my issue. I am attempting to match entries of money being written
off an account to the businesses GL File. However, when these write offs are
done what is typically a link is broken. However, by adding the dollar
amount written off I can get all of the write offs and GL entries for the
same dollar amount and that day. The GL has a ref number for each
transaction. What I am trying to do is assign one reference number to one
write off, regardless of how many times it may have happened that day.

The reference number to entry match is not as important as seeing what did
not match at the end of the project, since i know I have the same number of
GL Ref# and write off entries for each, I simply need to populate it, the
order is not important.

Fields I am working with
Write Off Transaction: GL Code:
Month Month
Day Day
Year Year
Amount Amount
Date GL Ref Num
WriteOffNumber

I can match by month, day, year and amount. I need to assign a unique GL
Ref to each unique combination of Date / WriteOffNumber.

Oh - I almost forgot, I am a VBA midget, but will attempt it if it is the
only way.

PLEASE HELP!!!!!!!

 

[KARL DEWEY]

First you need to use one DateTime field for the Month, Day, and Year
information.

Post an example of what your 'unique combination of Date / WriteOffNumber'
would look like. What does your ' GL Ref Num' look like?

 

[Gkann]

I will show you examples from both:

This is from an AS/400 so forgive the date format, it was not my idea.
Write Off Transaction
Date Ref Number GL Ref# Amount
1080318 123456 0 $150.00

If the transaction was not a write off, then an actual GL Ref# would be
placed in that field. However, someone thought it a good idea to skip that
step when doing write offs!!!!!????

GL Transaction:
Posting Year Posting Month Month Day Year GL Ref# Amount GL Account
2008 3 3 18 2008 45112 $150.00
225

The transaction in the GL will get a reference number, but that reference
number is not tied back to the write off transaction that created it.
Posting month and year are provided seperate as the accounting month ends on
the 25th.

What I did so far was to change the above date into the month day year
fields, and match by dollar amount. However, the GL does not have anything
to the right of the dec point, and there can be several write offs for $12 on
the same day.

The order of which write off gets which GL entry does not matter to me, I
simply need to assign them so I can show transactional details for each GL
entry.

As a last resort I am thinking about pulling both sides into excel and
sorting them then copying and pasting, importing back into access. If that
sounds easier just let me know.

I hope this helps.


Thanks for the reply Karl.

Gary

 

[KARL DEWEY]

I do not follow this at all. Maybe someone with financial background can help.

 

[Gkann]

Karl,

I understand. Please allow me to remove the financial aspect of the problem.

The first table and values:

Letter Number
A 10
B 10
C 20
D 20


Second table:
Number Color
10 Green
10 Blue
20 Yellow
20 Red

I need to have a resulting table that looks like this:

Letter Number Color
A 10 Green
B 10 Blue
C 20 Yellow
D 20 Red

The letter / color combination could be reversed as long as both values also
had the same number value. The key is I need to have all of these matches
listed only once for both the letter and the number. A normal query would
give me:

A 10 Green
A 10 Blue

The balance of the issue I can figure out for myself, but this seems to be
something that there should be some simple trick to handle, I just can not
figure it out.

Thanks for your help!

 

[KARL DEWEY]

The results are going to look like this --
Letter Number Color
A 10 Green
A 10 Blue
C 20 Yellow
C 20 Red
B 10 Green
B 10 Blue
D 20 Yellow
D 20 Red
If you want 'A' to show only one then how do you determine which color is to
be shown with it?

 

[Gkann]

For the sake of this project it does not matter. If the letters where users,
and numbers transaction amounts, and the colors are account numbers, I simply
have to show that the same number of transactions exist for the same amount
in both tables on the same day. For the purposes of my project who entered
which transaction that day does not matter, it only matters that I can show
matching.

As an alternative I started looking at doing a count by dollar amount, along
with a sum of the dollar amount, and attempting to match a summary. Although
this way is not very clean, it has moved me off center. However, if I could
go back to my orignial thought, that would be prefered.

I hope I am not making a mountain out of a mole hole.

Thanks,

Gary