Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2013
    Posts
    3

    Unanswered: Requesting Guidance - Query Design

    Hi there all. This is my first post, but I've read some good info here before registering. I appreciate the knowledgeable and helpful community.

    Here's a foreward to give some insight to what I'm trying to do:
    I work in Accounts Payable for a print-advertisement placement agency. We have recently been branching out to place digital ads (online) and pre-print ads (inserts sent along with a newspaper that are placed on top of the paper, or inside a section, whatever the specific situation is) have always been placed but are handled manually (increasing volume is making this a pain). The way the billing works on pre-print ads and digital ads is we agree upon a rate for the ad based on the number of impressions or insertions (say for example $100 for 100,000 impressions). After the job is complete, for whatever reason, the actual circulation # will differ from the contracted # of impressions, almost always LESS than what was contracted. Once we enter this updated circulation # into our enterprise system, the system updates the cost for the job by multiplying the "rate per impression" by the # of impressions. For whatever reason, pre-prints also have a system-generated credit entered in for the ad (If only 95,000 impressions run, the total cost should be $95. The system reduces the cost to $95, then adds a $5 credit [don't ask why, I have no idea and can't get any straight answers]), which results in double-counting of the discount.

    Now to Access: Our SQL server pulls all this data from our enterprise system and Access allows us to create custom queries and manipulate the data easier than getting IT to update the enterprise system if we want a new view or special query. We use Access for reporting purposes only, and when we get the info we need we take that elsewhere to complete the process. I attached a screenshot that shows what I'm trying to do. Basically, the "client name" field will also contain a string of text indicating special cases like digital ads and preprint ads (ex: ABC Company - Preprints). I want to add a field to our reporting query that will calculate the $ left to pay, but each situation (Digital, Preprint, Standard "ROP") is handled differently. For Digital and Standard ROP, it's our "OrderNTP-PaidNTP" fields (no prob calculating those individually). For Preprints, it's just "OrderNTP".

    In my mind, I basically just need a simple If statement: If(client name contains the word "preprint" OR "preprints" OR "pre-print",calculate it this way,handle it this way). I know what I have in the screen cap is not the correct syntax but I don't know how to do it and I figured that was relatively straight-forward with my intentions.

    Sorry for the long post, I hope someone has some guidance with how I should be building this. Let me know if you need any more info.
    Attached Thumbnails Attached Thumbnails msaccess.png  
    Last edited by db-noob; 07-12-13 at 16:36. Reason: MS Access 2007

  2. #2
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Sorry, but your textual explanation is confusing, to me, and the posted attempt doesn't seem to follow that explanation, at all, but in general, rather than using In(), I would be to use the Instr() Function:

    Code:
    IIf((InStr([Client name], "preprint") > 0) Or (InStr([Client name], "pre-print") > 0), Calculate Like This, Else Calculate Like That)


    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  3. #3
    Join Date
    Jul 2013
    Posts
    3
    Missinglinq:

    Sorry for the confusing post -- the point of all the backstory is that I'm trying to handle several different cases in different ways, all within the same field. I could handle the situation by designing different queries, but it's much more convenient and time-efficient to have everything being reported in the same query.

    Regardless, I think you're right on about the InStr function, and I appreciate the heads up. When I try to run the query with the formula you suggested:

    AdType: IIf((InStr([ClientName],"preprint")>0) Or (InStr([ClientName],"pre-print")>0),"Preprint","Not Preprint")

    I get an error, as seen in the attached screen cap. I'm not sure why it's giving me the error, but it seems to me the syntax is accurate?

    I'll eventually switch the "preprint" and "not preprint" commands over to actual calculations as I stated in the initial post, I just figured I'd use a simpler command until I get the first part of the function working.
    Attached Thumbnails Attached Thumbnails syntaxerror.png  

  4. #4
    Join Date
    Jul 2013
    Posts
    3
    Hey all,

    Here's a quick update:

    I returned to design view to see if I couldn't monkey around with the way I was entering the query to avoid the error I was receiving. I ended up removing the "Table" reference that I had entered and the query ran without issue, and worked just as I expected! Screenshot is attached -- for now I'm just testing whether the "ClientName" contains "preprint" in the string (as Missinglinq suggested) and reporting "preprint" or "not preprint" accordingly, but I'll use the billing information now and shouldn't have any issues.

    Thank you Missinlinq for your help
    Attached Thumbnails Attached Thumbnails preprint.png  

  5. #5
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Glad we could help!

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •