Page 1 of 2 12 LastLast
Results 1 to 15 of 18

Thread: Query Help

  1. #1
    Join Date
    Sep 2008
    Posts
    20

    Unanswered: Query Help

    Hi Guys,

    I have the following MYSQL Code for a query:

    SELECT [Individual Log].[Log Id], [Individual Log].Date, [Individual Log].Staff, [Individual Log].Additions, [Individual Log].Deletions, [Individual Log].Renewals, [Individual Log].Lapses, [Individual Log].Invoicing, [Individual Log].Underwriting, [Individual Log].Changes, [Individual Log].[New Group], [Individual Log].[New Group Members], [Individual Log].[Customer Requests], [Individual Log].Urgents, [Individual Log].Quotes, [Individual Log].[Quoted Members], [Individual Log].[Calls Received], Staff.[Hours Per Day]
    FROM [Individual Log] INNER JOIN Staff ON [Individual Log].[Log Id] = Staff.[Staff Id Number]
    WHERE ((([Individual Log].Date) Between [Please Enter From Date] And [Please Enter End Date]));


    It is basically a parameter query on a table where the date is between two parameters.

    There is also a field in there that is Staff.[Hours Per Day] i have just added this from a table called Staff and the field is obviously Called Hours Per Day.

    I want this field to contain the correct info depending on the staff member that the query finds in the results when the query is run (The individual log and staff tables have the same info for the staffs names)

    I think the mysql can just be changed to input this? Im I Wrong? ) Help!

    Cheers

    Andy

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    first of all, a small point -- MySQL is the name of a database system that has nothing to do with Microsoft Access

    now, to the heart of the problem -- what exactly are you trying to do?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Sep 2008
    Posts
    20
    I Never said it did, I said that was the MYSQL code behind the query, I thought it would have been easier to see what i was doing that way!?


    I have 2 tables 1 called Staff and 1 Called Individual Log

    In a parameter query for the individual log table (parametered by date) i want the query to pull out the correct [hours per day] info from the staff table for each record found during the query.

    The query can reference and find the correct [hours per day] in the staff table by staff name.
    As the staff table contains:

    Staff Name ~#~ Fields linked - this table provides names for individual log
    Hours per day
    ... plus others

    And the individual log contains:

    Staff ~#~ Fields Linked - this table takes names from staff Table
    Hours Per Day
    ... plus others


    What do you think?

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    what do i think? i dunno, i can't see the tables from here

    you have a JOIN in the query, which matches [Individual Log].[Log Id] with Staff.[Staff Id Number] -- are you saying that this doesn't work?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Sep 2008
    Posts
    20
    Why are you being so sarcastic?! id rather sort it out without you thanks!

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    what i meant was, i can't see your data so i don't know whether your JOIN, as written, would actually work -- or indeed whether this was even what you needed help with, because what you needed help with wasn't very clear
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Sep 2008
    Posts
    20
    Ok, I'm gonna try and do this another way.

    I have a form for a table both called [Individual Log]

    I have on that form/table a textbox that is Called [Hours Per Day]

    What i want to happen is: When that text box gets focus,

    I want it to find the Hours Per Day info from a form/Table called [Staff]
    Which also contains the field [Hours Per Day] (Preset with the info)

    it needs however to find the correct hours per day info from the staff table from the record that corresponds with the staff name on both tables

    I'm so sorry i can't explain it very well!!!!

    Let me put it another way too!

    I want the Individual log form to pull the hours per day figure for the correct staff member from the staff form
    - Obviously the staff form/table contains all the staff with the hours per day for each staff member and the individual form contains the staff member name.

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i'm sorry, i'm going to have to bow out of this thread

    i do queries, but i don't do forms
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Sep 2008
    Posts
    20
    Thanks for trying! Anyone else? hehe

  10. #10
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Sounds like you just need to either:

    Relate the staff table in so you have live access to the detail or

    Use a DLookup function to fetch the data from the staff table.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  11. #11
    Join Date
    Sep 2008
    Posts
    20
    What is a Dlookup? its sounds promising?

    Thanks for your help

  12. #12
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Is there anyway of using the acronym RTFM without sounding nasty?

    Perhaps if I omit the F...
    RTM?
    George
    Home | Blog

  13. #13
    Join Date
    Sep 2008
    Posts
    20
    Ok i have tried to use the DLookup but i need to reference by a field that is not a primary key:


    StaffNameChosen = Staff.Text

    hpd = DLookup("[Hours Per Day]","Staff","[Staff Name]"= StaffNameChosen)

    Staff Name is not the primary Key and i Cant'd Get the Staff Id Number from the form I am using as the combo box i am using stores the staff name chosen into the table not the ID Number because ut being used to create reports that cannot change.

    The Combo Box i am using (Staff.Text) Therefore has this as the row
    source

    SELECT Staff.[Staff Name], Staff.[Staff Name] FROM Staff;
    I have been stuck on this for days!

  14. #14
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by georgev
    Is there anyway of using the acronym RTFM without sounding nasty?
    "please read the fine manual"
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  15. #15
    Join Date
    Sep 2008
    Posts
    20
    This Line of code gives the following error

    hpd = DLookup("[Hours Per Day]", "Staff", "[Staff Name] =" & Forms![Individual Log]!Staff)


    Run-time error 3075

Posting Permissions

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