#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2011
    Posts
    4
    Rep Power
    0

    Need a SQL genius


    Can someone with SQL knowledge help me out?
    So the question goes like this:

    NAME DATE PRESENTS
    bob march 3 2011 1
    bob june 6 2008 2
    bob jan 3 2012 3
    mary feb 14 1986 4
    mary april 10 2001 5
    mary jan 3 2012 6
    kate march 3 2011 7
    kate jan 3 2012 8
    kate oct 9 2013 9
    celia march 3 2011 10
    celia feb 14 1986 11
    celia july 4 2011 12
    celia jan 3 2012 13
    celia feb 14 1991 14

    So the goal is we add the amount of presents kate and celia got on the same days if they got any on the same days
    What I need to do is kind of like this except with a much bigger data set. Prolly in the 100 000 entries.
    i need the answer in SQL code???? or access 2003 query SQL

    Thanks!
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,172
    Rep Power
    4274
    Originally Posted by klondikegurl
    So the goal is we add the amount of presents kate and celia got on the same days if they got any on the same days
    just these two girls?

    also, this is the MySQL forum (which is not the same as MS SQL), so i'll move your thread to the Database Management forum (which is where the MS Access questions go)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2011
    Posts
    4
    Rep Power
    0
    yes just these two. there are other data i have to deal with of the sort but this example should give me an idea of what to do . and thank you for moving this to the right forum!
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2010
    Posts
    128
    Rep Power
    71
    Originally Posted by klondikegurl
    yes just these two. there are other data i have to deal with of the sort but this example should give me an idea of what to do . and thank you for moving this to the right forum!
    Hi,
    Just check out if below gives some guidelines :

    Code:
    SELECT 
    qryKatePresents.txtName, 
    qryKatePresents.dtDate, 
    qryKatePresents.KatePresents, 
    qryCeliaPresents.txtName, 
    qryCeliaPresents.dtDate, 
    qryCeliaPresents.CeliaPresents, 
    [qryKatePresents.KatePresents]+[qryCeliaPresents.CeliaPresents] AS TotalPresents
    FROM 
    (
    SELECT tblPresents.txtName, tblPresents.dtDate, tblPresents.Presents AS KatePresents
    FROM tblPresents
    where (((tblPresents.txtName)="kate"))
    )
    as qryKatePresents 
    INNER JOIN 
    (
    SELECT tblPresents.txtName, tblPresents.dtDate, tblPresents.Presents AS CeliaPresents
    FROM tblPresents
    where (((tblPresents.txtName)="celia"))
    )
    as qryCeliaPresents 
    ON 
    qryKatePresents.dtDate = qryCeliaPresents.dtDate;
    Thanks

    Comments on this post

    • klondikegurl agrees
  8. #5
  9. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,172
    Rep Power
    4274
    holy complexity, batman

    hey recyan, where did those query/table names come from?

    has klondikegurl been posting the same problem on other web sites?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  10. #6
  11. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2011
    Posts
    4
    Rep Power
    0
    to r937 yes i have been posting in a few places but recyan gave me a unique answer

    to recyan! thank you. if you could I made the example as a sum to make it different than the original problem. It was originally well everythign else the same btu a subtraction say subtract kaets presents from celias that they got on same day . Would the code change very much or jsut replace inner join with something else? and also i think inner join is for tables with no duplicate values but because this is such a large dataset there may be some values in a volumn that will repeat I'm not sure if that affects anything?

    Thanks again
  12. #7
  13. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,172
    Rep Power
    4274
    i had a feeling your original post was not showing the real data or the real problem...

    ... so now i don't feel too bad about not even attempting to answer it, because my solution would assuredly not have been what you really wanted, even if it answered the question you posted

    have fun

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  14. #8
  15. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2011
    Posts
    4
    Rep Power
    0
    Originally Posted by r937
    i had a feeling your original post was not showing the real data or the real problem...

    ... so now i don't feel too bad about not even attempting to answer it, because my solution would assuredly not have been what you really wanted, even if it answered the question you posted

    have fun

    are u not able to attempt to give me a solution absed on the criteria that it is a large dataset with maybe soem repeating values for presents in columns and that it is a subtracton of presents rather than addition? for each day that kielia and kate both get presents? i think that would really help me if you would try to attempt to help . my work requires privacy so i am not able to provide real data etc it would be real kind of you if you would help
  16. #9
  17. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2010
    Posts
    128
    Rep Power
    71
    r937
    hey recyan, where did those query/table names come from?
    I have this tedious way of doing things, Still can't understand my own queries unless I use descriptive table, fields and subqueries names.

    r937
    has klondikegurl been posting the same problem on other web sites?
    I really don't know.
    Am active member ( average visits of 2-3 times a week) of aspfree ( under a different name ) which I joined to learn asp and this forum which I have joined to learn php & mysql. Don't remeber seeing the OP's post ( might have missed it ) at aspfree though I must say they have quite a few access gurus there & I think the OP would have had the question answered there itself if it had been posted.

    klondikegurl
    It was originally well everythign else the same btu a subtraction say subtract kaets presents from celias that they got on same day .
    Just check out if below gives some guidelines :
    Replace
    Code:
    [qryKatePresents.KatePresents]+[qryCeliaPresents.CeliaPresents] AS TotalPresents
    with
    Code:
    [qryCeliaPresents.CeliaPresents]-[qryKatePresents.KatePresents] AS DifferenceInPresents
    in the previous code.

    klondikegurl
    because this is such a large dataset there may be some values in a volumn that will repeat I'm not sure if that affects anything?
    By duplicate values, I assume that for eg. Kate has received presents more than once on the same day.
    The first thing that comes to mind is whether duplicate values in your tables are allowed to be there functionally or are a result of erroneous input and should actually be removed.
    If they are a result of erroneous input then the focus should be on removing them.
    If they are allowed then check out if below gives some guidelines :

    Code:
    SELECT 
    qryKatePresentsSum.txtName, 
    qryKatePresentsSum.dtDate, 
    qryKatePresentsSum.KatePresentsDateTotal, 
    qryCeliaPresentsSum.txtName, 
    qryCeliaPresentsSum.dtDate, 
    qryCeliaPresentsSum.CeliaPresentsDateTotal, 
    [CeliaPresentsDateTotal]-[KatePresentsDateTotal] AS PresentsDiff
    FROM 
    (
    select 
    tblPresents.txtName,
    tblPresents.dtDate,
    Sum(tblPresents.Presents) AS KatePresentsDateTotal 
    from 
    tblPresents 
    group by 
    tblPresents.txtName,tblPresents.dtDate 
    having 
    (((tblPresents.txtName)="kate")) 
    order by tblPresents.dtDate
    )  
    AS qryKatePresentsSum 
    INNER JOIN 
    (
    select 
    tblPresents.txtName,
    tblPresents.dtDate,
    Sum(tblPresents.Presents) as CeliaPresentsDateTotal 
    from 
    tblPresents 
    group by 
    tblPresents.txtName, tblPresents.dtDate 
    having 
    (((tblPresents.txtName)="celia")) 
    order by tblPresents.dtDate
    )  
    AS qryCeliaPresentsSum 
    ON 
    qryKatePresentsSum.dtDate = qryCeliaPresentsSum.dtDate;
    Pls Note : Request you to present all the facts involved as you are indicating a large database and the Gurus here might be be able to foresee pitfalls that you and I might not see.

    Thanks

IMN logo majestic logo threadwatch logo seochat tools logo