Results 1 to 8 of 8
  1. #1
    Join Date
    Mar 2003
    Posts
    225

    Unanswered: data not showing where no records

    I have 2 tables...

    tblEvents
    tblFMForEradication

    i am trying to run a query to generate the number of events that have happened for each code that appears in the tblFMForEradication table.

    however when i run the query i only get 17 records returned wheras there are 30 records in the tblFMForEradication table.

    If the code has not occured i need it to display zero,

    but i always need all the codes in the tblFMForEradication to be listed in this case there should always be 30 records in the list.

    please help

    Andy
    Attached Files Attached Files

  2. #2
    Join Date
    Jun 2006
    Posts
    103
    Quote Originally Posted by Chimp8471
    I have 2 tables...

    tblEvents
    tblFMForEradication

    i am trying to run a query to generate the number of events that have happened for each code that appears in the tblFMForEradication table.

    however when i run the query i only get 17 records returned wheras there are 30 records in the tblFMForEradication table.

    If the code has not occured i need it to display zero,

    but i always need all the codes in the tblFMForEradication to be listed in this case there should always be 30 records in the list.

    please help

    Andy
    I have read your database, your query seems ok, the problem on why u get 17 records out of 30 is because of this

    WHERE (((tblEvents.DayCode) Between 6148 And 6154))

    it turns out some of the DayCode missing because they are not within this range


    I have add the database relationship for u, and now u can check on
    tblFMForEradication, in the first field of the table, u will see a '+' sign, click on it, it will refer those record which are from tlbEvents, i have check every records in tlbEvents, all refer at least one records to table tblEvents

    if u want to find out what records is missing

    try this

    SELECT tblFMForEradication.Eventcode
    FROM tblFMForEradication
    WHERE Eventcode not in (select Eventcode from query1)

    this query will get u 13 records of event code which are missing

    hm...if u want to put all zero count to those 13 that is missing, there is a trick, use the query that i give u which is 'Query3'

    change the SQL statement to

    SELECT tblFMForEradication.Eventcode, 0 AS Count
    FROM tblFMForEradication
    WHERE (((tblFMForEradication.Eventcode) Not In (select Eventcode from query1)));

    then

    do a union on query1 and query3, u will get the result, but u need to work out the inner join to get the Line field on query3
    Attached Files Attached Files
    Last edited by mkggoh; 06-16-06 at 06:05.

  3. #3
    Join Date
    Mar 2003
    Posts
    225
    thanks for that, so is there no way to get all 30 records displayed from the tblFMForEradication if there is no record a zero put in place instead?

  4. #4
    Join Date
    Jun 2006
    Posts
    103
    I cant say A 'No', this is the only way. Of course there are many way to get a job done, for now, i only have this idea that will solve your problem, there might have some creative ppl out there who can offer better advise.

    U can try my suggestion if u dont have any other.

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by Chimp8471
    thanks for that, so is there no way to get all 30 records displayed from the tblFMForEradication if there is no record a zero put in place instead?
    How does this do you?
    Code:
    SELECT Events.Line, tblFMForEradication.Eventcode, Count(Events.FaultcodeLognumber) AS [Count]
    FROM tblFMForEradication LEFT JOIN (SELECT * FROM tblEvents WHERE DayCode Between 6148 And 6154) AS Events ON tblFMForEradication.Eventcode = Events.EventCode
    GROUP BY Events.Line, tblFMForEradication.Eventcode
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Mar 2003
    Posts
    225
    that would appear to work......

    however 2 problems....

    in the fields that have zero's the line name doesn't appear..

    also

    would i be able to change the date range easy enough or would i have to go into the sql each time?

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by Chimp8471
    in the fields that have zero's the line name doesn't appear..
    Not much you can do about that. You want to display records in tblFMForEradication but that do not appear in tblEvents so how can you display information from tblEvents for those recrods?

    Quote Originally Posted by Chimp8471
    would i be able to change the date range easy enough or would i have to go into the sql each time?
    No - but the best way depends. Is this run from a form? If so you could put the day ranges in two text boxes and refer to those in the SQL. Or you could prompt the user:
    Code:
    SELECT Events.Line, tblFMForEradication.Eventcode, Count(Events.FaultcodeLognumber) AS [Count] FROM tblFMForEradication LEFT JOIN (SELECT * FROM tblEvents WHERE DayCode Between [Please enter a Start Day Code] And [Please enter an End Day Code]) AS Events ON tblFMForEradication.Eventcode = Events.EventCode GROUP BY Events.Line, tblFMForEradication.Eventcode
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I'd suggestr taking a few steps at a time....

    proove your sql is working (ie giving all relevant records) then move on to how do I tweak this query)

    ....fyi its fairly easy to build parameters into queries. you can either add the parameters to the query design, or a better solution is to pull the values from another form using forms!<myformname>!<mycontrolname>.

    If you search for "parameter query" using the advance search facility, and limit results to Microsoft Access then you should see plenty of posts.
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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