Results 1 to 5 of 5
  1. #1
    Allison is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2011
    Posts
    3

    Query to perform several special operations

    I am making this database for a project that has to update monthly revenue and create a Journal Entry based on a business ledger report for different revenue categories (streams). I have created an import table that imports from recurring excel source (containing ledger report) and updates the main table that holds the current month records. The records represent revenue by client campaign, each having a unique Campaign Master IDs (my primary key). It is all pretty straightforward and being a novice I am pretty proud I managed to create this automated monthly update using these two tables and a pair of queries all running within a simple macro.

    However, the problem I run into is due to specific requirements for the Journal Entry format that actually needs to be derived from the input format (that I import from Excel and maintain in my main update table).
    Journal Entry has to be have several columns that have to be
    -populated by text or numerical constants
    (column 1 should always read "General",


    column 2 should always read "Revenue",
    column 5 should always read "invoice",
    column 15 should alwaye read "0100")
    -blank placeholders (with null values)
    (columns 11 & 12)
    -value based on coversion table
    (column 14)
    -either invoice number from input field or (if invoice # is blank) a composite of a posting date and account number
    (column 6)
    -either customer or G/L account type based on the type of customer
    (column 7)
    etc.
    What determines these is basically whether I have values in the import table columns that determine the type of revenue so I guess my question is how to write a query that performs all these special operations (I described above). I have a feel these are some sort of IF statements but how exactly to implement this logic is what I am struggling with.

    I also attached my database in case my descriptions are not all that clear.
    tblExportJournalEntry is what I should end up with after processing my tblMainUpdateTable the way I tried to explain above.


    I really appreciate any help and am grateful in advance.

  2. #2
    NTC is offline VIP
    Windows Vista Access 2007
    Join Date
    Nov 2009
    Posts
    2,392
    I don't fully understand your post. But in general terms; 'should always read ' can be handled via a default value at the table field property level; and the rest of the requirements seem to be achievable via a calculated value in a query. A query is a good point to manipulate strings & values of fields.

    Hope this helps a little.

  3. #3
    Allison is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2011
    Posts
    3
    Yes! The Default value is what handles the constant! Thank you, that helped and took care of most of the output requirements.

    As far as the rest of the requirements, how would those calculated fields in the query be approached to handle conditional portions of my requirements?
    For example - if the value in the revenue field "prepaid" in my input (tbl ImportPlatformFormat that updates to tbl MainUpdateTable) is >0 then take the businessID from the table add letter "P" and concatenate with the posting date and put in the Document Number field in the output format? I think a hint for the above example would set me on the way to figure out the rest of those.

    I know that my post might be somewhat confusing due to terms I used to describe since I am trying to grasp the precise terminology.
    However, I hope I can answer the specific questions you might have to clarify my points and still get the help I really need?

    Thanks in advance again.

  4. #4
    NTC is offline VIP
    Windows Vista Access 2007
    Join Date
    Nov 2009
    Posts
    2,392
    In a calculated field, in a query, there is no if/then and so one must use the iif method; you will want to look that up and get an example of the syntax. they can be nested..... and it can become confusing.

    one can string together fields easily simply by:
    NewField: [FieldName1] & "add some words" & [FieldName2]

    combined with iif you can pretty much manipulate the values, within a single row of data, as you need.

  5. #5
    Allison is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2011
    Posts
    3
    Yes, this hint did the trick! I managed to figure out the rest myself, I knew I just needed a spur in the right direction!

    Thank you all for your help.
    Last edited by Allison; 05-20-2011 at 10:41 AM. Reason: found solution

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 04-18-2011, 02:46 PM
  2. Perform an operation on multiple files.
    By newuser in forum Access
    Replies: 9
    Last Post: 11-18-2010, 11:21 AM
  3. Replies: 2
    Last Post: 11-18-2009, 06:49 PM
  4. Hi. I need very, very special help for Ms Access!
    By accessnotknow in forum Access
    Replies: 0
    Last Post: 06-12-2009, 12:21 PM
  5. Special Characters
    By orgelizer in forum Access
    Replies: 0
    Last Post: 03-20-2007, 08:24 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums