Results 1 to 13 of 13
  1. #1
    Join Date
    Mar 2007
    Posts
    91

    Unanswered: Displaying only the dates that exist for the specific customer (screen)

    Hi

    Can you please help me with my code. I'm trying to display only the dates that are available in the history table fo the customer displayed in the screen & it's not working

    Here's my code:

    SELECT DISTINCT date FROM Sasol_campaign_history WHERE sasol_master_campaign.[store name]=sasol_campaign_history.[store name];

    and i tried this as well

    SELECT DISTINCT date FROM Sasol_campaign_history WHERE sasol_campaign_history.[store name] = [forms]![frmSasol_campaign]![store name];

    it's in the row source

    Please kindly help

    Noks

  2. #2
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    SQL is a long ways from being my strong suit, but I think maybe

    SELECT DISTINCT Sasol_campaign_history.date FROM Sasol_campaign_history WHERE sasol_master_campaign.[store name]=sasol_campaign_history.[store name];

    Also, if you actually have a field named date you're heading for heartache! It's a reserved word and a dangerous one. Take a look at this :

    http://www.thescripts.com/forum/thread659167.html

    Good Luck!

    Linq
    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
    May 2004
    Location
    New York State
    Posts
    1,178
    Where's the join?

    SELECT DISTINCT date FROM Sasol_campaign_history WHERE sasol_master_campaign.[store name]=sasol_campaign_history.[store name];

    is missing the INNER JOIN ... ON .... clause.

    You need something like

    Code:
    SELECT [date] FROM Sasol_campaign_history INNER JOIN sasol_master_campaign ON sasol_campaign_history.[store name] = sasol_master_campaign.[store name] GROUP BY [date];
    Although I agree with Missinglinq that you shouldn't use "date" as a variable name because it's a reserved word.

    HTH,
    Sam

  4. #4
    Join Date
    Mar 2007
    Posts
    91
    Thanks Linq

    I've changed date to txtdate and now i get the same date for every customer even the 1s that don't have history

    Please help
    Noks

  5. #5
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    noks,

    You didn't see my post above.

    Sam

  6. #6
    Join Date
    Mar 2007
    Posts
    91
    Quote Originally Posted by Sam Landy
    Where's the join?

    SELECT DISTINCT date FROM Sasol_campaign_history WHERE sasol_master_campaign.[store name]=sasol_campaign_history.[store name];

    is missing the INNER JOIN ... ON .... clause.

    You need something like

    Code:
    SELECT [date] FROM Sasol_campaign_history INNER JOIN sasol_master_campaign ON sasol_campaign_history.[store name] = sasol_master_campaign.[store name] GROUP BY [date];
    Although I agree with Missinglinq that you shouldn't use "date" as a variable name because it's a reserved word.

    HTH,
    Sam
    Thanks Sam

    I tried yo code. It displays the date in each and every record (customer) even if it's not in the history table. it's like the where clause is ignored

    Noks

  7. #7
    Join Date
    Mar 2007
    Posts
    91
    Quote Originally Posted by Sam Landy
    noks,

    You didn't see my post above.

    Sam
    Just noticed it & responded

    Thanks

  8. #8
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    Quote Originally Posted by noks
    Thanks Sam

    I tried yo code. It displays the date in each and every record (customer) even if it's not in the history table. it's like the where clause is ignored

    Noks
    I specifically didn't put in a WHERE clause; it's redundant. Also, the INNER JOIN ... ON ... should have prevented the overlap of records. Please copy and paste the actual SQL statement in your code.

    Sam

  9. #9
    Join Date
    Mar 2007
    Posts
    91
    Quote Originally Posted by Sam Landy
    I specifically didn't put in a WHERE clause; it's redundant. Also, the INNER JOIN ... ON ... should have prevented the overlap of records. Please copy and paste the actual SQL statement in your code.

    Sam
    Here's my code:

    SELECT [txtdate] FROM Sasol_campaign_history INNER JOIN sasol_master_campaign ON sasol_campaign_history.[store name]=sasol_master_campaign.[store name] GROUP BY [txtdate];

    But it's still dispalying it in each adn every record

  10. #10
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    Quote Originally Posted by noks
    Here's my code:

    SELECT [txtdate] FROM Sasol_campaign_history INNER JOIN sasol_master_campaign ON sasol_campaign_history.[store name]=sasol_master_campaign.[store name] GROUP BY [txtdate];

    But it's still dispalying it in each adn every record
    The SQL looks good. Hm'm. Do you perhaps have duplicate store names in the master campaign table, or are they all unique?

    Sam

  11. #11
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    I've changed date to txtdate
    Changed it where? You can't just change it in the query.
    Hope this helps!

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

    All posts/responses based on Access 2003/2007

  12. #12
    Join Date
    Mar 2007
    Posts
    91
    I've changed it in the table as well as the query.

    Noks

  13. #13
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Shouldn't...

    "SELECT [txtdate] FROM Sasol_campaign_history INNER JOIN sasol_master_campaign ON sasol_campaign_history.[store name]=sasol_master_campaign.[store name] GROUP BY [txtdate]"

    Be...

    "SELECT [txtdate] FROM Sasol_campaign_history INNER JOIN sasol_master_campaign WHERE sasol_campaign_history.[store name]= '" & Forms!MyFormName!MyStoreFieldName & "' GROUP BY [txtdate]"

    (with the = ' " & Forms!MyFormName!MyStoreFieldName & " ') But maybe I missed that in the posts. I mean, wouldn't you want to make the criteria for the select statement based upon the field in the form called StoreName? I'm assuming you have a field on the form called StoreName.
    Last edited by pkstormy; 07-02-07 at 14:29.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Posting Permissions

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