Re: query returning either nothing or multiples of records

Tech-Archive recommends: Fix windows errors by optimizing your registry

Thank you so much for your help i really do appreciate it and sorry I keep
explaining my self poorly.

I understand that the procedure in identifying the problem is to break it
down and then build it back again piece by piece. Having undertaken this
process I am unable to pinpoint one join.

When the generic asset and transaction table are the only two in the query
the query works fine. However when the other three tables are included, the
relationship between generic assets and transaction tables results in no
records being returned.

Also the link between Transaction and ANZSIC Junction Results in the records
returned being increased from 13 to 23, there are 13 records in ANZSIC
junction and 23 records in transaction and ANZSIC Junction

I have gone through and made sure that all tables have records in pertaining
that are in the other tables and should therefore ensure that something is
always returned from the query.

General Question: Can the inclusion of a table and relationship in the upper
pain of the query screen alter the results of a query even though there are
no fields being used in the lower pain of the query design screen?


Allen Browne wrote:
>Good: the 2-table query works fine.
>
>If the 3-table query returned 21 records because there are 21 records in
>[ANZSIC Junction], then that makes sense.
>
>The approach I am suggesting for you to try is to take a mouthful at a time.
>If adding the 4th table returns no records, then investigate what happens if
>you build up with that table, starting with just 2 tables and watching the
>joins. If it suddenly returns no records, you have identified where the
>error lies.
>
>It is theoretically possible that none of the entries in [ANZSIC Junction]
>have any matching entries related through the other tables, and so there are
>no records to return. It is also possible that the joins between the tables
>(in the upper pane of the query design window) are incorrect, and that
>changing them will solve the problem.
>
>> Thank you both for your responses, I apologise for my ignorance but I have
>> a
>[quoted text clipped - 93 lines]
>>>> the
>>>> records being more than halved.


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200508/1
.

Relevant Pages

  • Re: query returning either nothing or multiples of records
    ... the 2-table query works fine. ... It is theoretically possible that none of the entries in [ANZSIC Junction] ... > Asset Name (Generic asset table), Industry Classification Name (Industry ...
    (microsoft.public.access.queries)
  • Re: query returning either nothing or multiples of records
    ... The answer to your general question is, ... You say the 2-table query works, but the 5-table query does not. ... > When the generic asset and transaction table are the only two in the query ... > junction and 23 records in transaction and ANZSIC Junction ...
    (microsoft.public.access.queries)
  • RE: Combo box bound field change
    ... Modify the row source to include both fields. ... Make the bound column the company name column. ... now you can change the company name any time and your query ... Make a copy of your transaction table, but copy only the structure, not the ...
    (microsoft.public.access.forms)
  • Re: Transaction Isolation Level
    ... This means that while I initiate a transaction selecting all new orders it ... My query is executed on a SQL server 2005. ... the isolation level you should use is snapshot isolation. ...
    (comp.databases.ms-sqlserver)
  • RE: Combo box bound field change
    ... now you can change the company name any time and your query ... Make a copy of your transaction table, but copy only the structure, not the ... Modify the New transaction table and change the company name field to ... I'd run a report to show what all that company purchased. ...
    (microsoft.public.access.forms)