 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Queries / March 2008Assigning Duplicate Values
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!!!!!!! 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?  SignatureKARL DEWEY Build a little - Test a little > Help - I am a long time lurker and first time poster. > [quoted text clipped - 27 lines] > > PLEASE HELP!!!!!!! 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 > First you need to use one DateTime field for the Month, Day, and Year > information. [quoted text clipped - 33 lines] > > > > PLEASE HELP!!!!!!! I do not follow this at all. Maybe someone with financial background can help. SignatureKARL DEWEY Build a little - Test a little > I will show you examples from both: > [quoted text clipped - 73 lines] > > > > > > PLEASE HELP!!!!!!! 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! > I do not follow this at all. Maybe someone with financial background can help. > [quoted text clipped - 75 lines] > > > > > > > > PLEASE HELP!!!!!!! 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? SignatureKARL DEWEY Build a little - Test a little > Karl, > [quoted text clipped - 116 lines] > > > > > > > > > > PLEASE HELP!!!!!!! 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 > The results are going to look like this -- > Letter Number Color [quoted text clipped - 129 lines] > > > > > > > > > > > > PLEASE HELP!!!!!!! |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.