Results 1 to 12 of 12
  1. #1
    Join Date
    Jun 2007
    Posts
    7

    Unanswered: need to write query comparing two tables and only showing me the correct results

    Hello,

    I have been asked to try and build an access database which will compare a customers invoice with what our current correct prices are to stop incorrect invoices being issued.

    I have one table which has the customer charges on it (calls) and the other holds the correct original charges.

    i have two fields in both which are the same and what i need to do is make a query which basically says:

    if field A and B in table one is the same as field A and B in table 2 then compare price field in table one and two and if not the same then show

    i have been studying programming for a while now but just did training on access last week so i just cant seem to get the syntax right and i know it should be super easy but i cant get it so anyones help would be most obliged!

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Having troubling picturing the problem.
    Can you post example data from your tables and your expected results?
    Your table structures would be useful too!
    George
    Home | Blog

  3. #3
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    something like:

    SELECT TABLE2.FIELDA, TABLE2.FIELDB, TABLE2.PRICE
    FROM TABLE1 INNER JOIN TABLE2 ON TABLE1.FIELDA=TABLE2.FIELDA
    AND TABLE1.FIELDB=TABLE2.FIELDB AND TABLE1.PRICE<>TABLE2.PRICE
    Inspiration Through Fermentation

  4. #4
    Join Date
    Jun 2007
    Posts
    7
    georgev i would love to but dont have permission to post the data i am afraid as it is not mine to post but RedNeckGeek seems to have what i was looking for and thankyou, i tried but still get errors but its my syntax i am sure because i followed the same logic as before but i will plod along and hopefully get it in the end, its telling me my syntax is wrong and that i have to add parenthesis strange but thanks again i cant believe how quicky i got an answer ill definitly be using the site again

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Hey, no problemo. I appreciate your reservations about posting data, I only wanted dummy data really but even that can be hard to produce in some situations. Good luck and feel frree to post back if you get stuck
    George
    Home | Blog

  6. #6
    Join Date
    Jun 2007
    Posts
    7

    ok i just cant get it :(

    Hey guys,

    So i have been wracking my brain on this query! My job sent me to an access course and now want me to build this god damned thing, i really should stop saying yes! so here goes i will explain a bit more:

    so i have one table which has matching fields which are 'call class' and 'tariff'

    i also have fields in tableA (CDRFILES) which hold fields named 'call duration' and the 'cost price' (in seconds)

    in tableB(RatesSP) i have a field 'call per minute'.

    what i am trying to do basically is ask the query to match the records with the same call class and tariff and then check that the price per call from table A is the same as a price per call would be in table B and to show me the records which are not the same.

    so this is the query i wrote:

    SELECT tbl.CDRFILES.Tariff, tbl.CDRFILES.Call Class, tbl.CDRFILES.Call Price, tbl.CDRFILES.Call Duration
    FROM tbl.RatesSP INNERJOIN tbl.CDRFILES ON tbl.CDRFILES.Tariff = tbl.RatesSP.tariff And tbl.CDRFILES.Call Class = tbl.RatesSP.call class And tbl.CDRFILES.Call Price / tbl.CDRFILES.Call Duration <> tbl.RatesSP.cents per minute / 60

    am i just way off or does anyone get what i mean?

  7. #7
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    ouch!

    SELECT tbl.CDRFILES.Tariff, tbl.CDRFILES.Call Class, tbl.CDRFILES.Call Price, tbl.CDRFILES.Call Duration
    FROM tbl.RatesSP INNERJOIN tbl.CDRFILES ON tbl.CDRFILES.Tariff = tbl.RatesSP.tariff And tbl.CDRFILES.Call Class = tbl.RatesSP.call class And tbl.CDRFILES.Call Price / tbl.CDRFILES.Call Duration <> tbl.RatesSP.cents per minute / 60


    before wasting any more time on this (yours, mine, every one else), please rewrite the SQL ERROR: my original reply said 'whole thing' but should have read 'SQL' so that any table or field name that includes spaces is enclosed in square brackets.
    i.e.
    my field name is this
    please re-write as
    [my field name is this]

    who knows - maybe it will even work first time.

    memo to the universe:

    do NOT use spaces in names of things
    MyFieldNameIsThis
    is perfectly understandable and doesn't need the eye-aching [blah blah] stuff

    izy
    Last edited by izyrider; 06-13-07 at 15:37.
    currently using SS 2008R2

  8. #8
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    wooo

    you have spaces in the wrong places and not in the right places!

    continue with the [my name is] fix.

    but you also need INNER JOIN rather than INNERJOIN

    izy
    currently using SS 2008R2

  9. #9
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    woooo^2

    you are (will be!) performing
    ...INNER JOIN tbl.CDRFILES ON tbl.CDRFILES.tarrif...

    mmm
    i'm not an SQL guru so maybe your proposal makes sense, but my non-guru view:
    this seems to suggest a table named 'tbl' (????)
    and that you will perform an INNER JOIN tbl ON tbl (????)
    and that you hope Access will understand [tableName].[fieldName].[WhatIsThisSupposedToBe] (?????)

    izy
    currently using SS 2008R2

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I'm betting a table called tbl.CDRFILES

    A very wise move would be to avoid all non-alpha characters (apart from _ instead of a space but some people balk even at that) in column & object names. I avoid numbers too since these do not necessarily scale well to other systems (SQL Server,for example, does not like numbers at the beginning, or end - I forget, of object names.

    There is a link around somewhere from MS recommending Access naming conventions.... Anyone got it?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  11. #11
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by pootle flump
    apart from _ instead of a space but some people balk even at that
    I hate underscores You should be able to write SQL statements almost like sentences (with very odd punctuation, and if you chose, capitalization). The underscore sits so badly in regular text. *shrugs*
    Quote Originally Posted by pootle flump
    SQL Server,for example, does not like numbers at the beginning, or end - I forget, of object names.
    I'm 95% sure it's the beginning and not the end
    Quote Originally Posted by pootle flump
    There is a link around somewhere from MS recommending Access naming conventions.... Anyone got it?
    http://www.dbforums.com/showthread.php?t=1618596
    George
    Home | Blog

  12. #12
    Join Date
    Jun 2007
    Posts
    7

    thankyou all!

    Hi guys,

    First of all a big thanks to all of you for the help it is really appreciated and you will be glad to know my query now works perfectly yey!

    It definitly was the naming conventions that where giving me trouble but i have now learned a valuable lesson and wont make the mistake again
    (i hope!)

    anyways i am sure you will all hear from me again as i am bound to get confused a lot more, i am a woman after all

Posting Permissions

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