Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2012
    Posts
    2

    Unanswered: Access 2007 query only working on certian records

    I have created a database, but on of the querys I have created is not functioning correctly.

    an old teacher of mine says the query is fine theres a problem with the data input, and i need to add a valadtion rule to solve the problem, though i don't know what kind of valadation to use.

    can anybody help?

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    yes
    one that appropriate validates your data, checks for valid values
    in access you can valdiate data by several means
    1) in the form using the before update event
    2) in the table design byt judicious use of the default value, input mask and or define a validation rule
    3) judicious use of indexes
    4) proper use of relational integrity

    but as we don't know your table design, and we don't know what validation you have to perform then I doubt we can offer much advice beyond the general

    in the absence of detail, such as the query that is failing to 'function correctly', that would be the actual SQL (in the query designer swicth to SQL view usually the button under the file option)
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Mar 2012
    Posts
    2
    This is a veiw of the query in sql view:

    SELECT TBLClients.[Full name], TBLJob.Title, TBLApplicant.[Full name], TBLInterview.[Interview ID], TBLInterview.Result
    FROM TBLApplicant INNER JOIN ((TBLClients INNER JOIN (TBLApplication INNER JOIN TBLInterview ON TBLApplication.[Application ID] = TBLInterview.[Application ID]) ON TBLClients.ClientID = TBLInterview.[Client ID]) INNER JOIN TBLJob ON (TBLClients.ClientID = TBLJob.[Client ID]) AND (TBLApplication.[Job ID] = TBLJob.[JOB ID]) AND (TBLInterview.[Job ID] = TBLJob.[JOB ID])) ON TBLApplicant.ApplicantID = TBLApplication.[Applicant ID]
    WHERE (((TBLClients.[Full name])=[Enter clients name]));

    The query is working on some records but not all of them.

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so what identifies rows that are correctly returned and those that are not returned.

    my guess is going to be that you have a a problem on your joins.probably that you are missing data in one column.

    fundamentally its your data, its your table design

    what relationships have you defined ?

    one of the principles behind a realtion database is data integrity.
    you also enforce data integrity through program validation. ferinstance ensure that you must have an address for a person/company. however its feasibale that you may not have an address when you create a record.

    the business rules may require you to analyse your data and identify riws which may have problems

    so if I were you I'd start off trying to identify what is failing, no one else can do thwsi for you. saying
    The query is working on some records but not all of them.
    is not sufficient I'd suggest you go through each join and work out if there are records missing because of missing values.

    understand the join types
    look at the joins in the query (right click the line linking tables and select the appropriate types (or just modify then and see what happens)
    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
  •