Query Help

[ProjectExplorer]

I have two tables - tblOrders and tblClaims.

A person can order forms for many claim claims.

We may therefore dispatch many forms to a client who may return any combination of the forms to us.We therefore only create a claim record (tblClaim) for each claim when we recieve a claim form back.

I have set up a one (tblOrders) to many (tblClaims)relationship.

How can I identify which orders have no claims records set up (bearing in mind that we only create a claims record if a client returns a claims form)? Basically finding orders with no claims records attached (non responders).

Any help appreciated.

 

[fneily]

I see three tables. tblClient tblOrders tblForms
tblClient has primary key ClientID, other client fields
tblForms has primary key FormID, other form field info
tblOrders has primary key OrderID, ClientID, FormID, Date, etc.

tblClient and tblForms can be pre-filled.
So your "transaction" table is tblOrders. This is filled in when you receive a claim form back.
Then you can run a comparison between tblOrders and tblForms to see which forms were not sent back by that order.

 

[ProjectExplorer]

fneily, thanks for your idea. Sorry, I do have a client table and a foreign client ID sitting in the orders table to create the link.

However, I don't believe the orders (which are really just enquiries) and forms relationshio will fit with my scenario.

E.g. A person phones in with an enquiry and requests (orders) 5 forms and retuns two of them a week later.

Initially I need to create the order record showing that 5 forms were dispatched. Then we are creating a record for each form returned (i.e. a new business record) and linking it back to the original order. The order can therefore have many forms linked to it.

However, I need to be able to identify how many clients have ordered forms (i.e. have an order record) but have not returned any forms back.- i.e. we have an order record (showing 5 forms dispatched) but there are no linked records in the forms returned table.

Hope this makes sense.

 

[lespaul]

So you have this:
[tt]
tblOrders
CustomerName OrderNumber OtherFields NumberOfForms
LesPaul 15 Address 5


tblClaims
CustomerName OrderNumber OtherClaimFields
LesPaul 15
LesPaul 15
[/tt]

and you want to know that the Customer LesPaul has 3 forms that haven't been returned?




Leslie

In an open world there's no need for windows and gates

http://www.ubuntu.com

 

[ProjectExplorer]

Yes, that's how I have the tables set up so I track the claims records back to their respective orders. The claims records are not created until each claim form comes back (we do not want to create lots of empty claims records where clients have not returned the forms).

At some point I will be asked to identify all orders where the clent has not returned a claim form.

I put put a "form back" yes/no field on the orders table but wondered if I needed to do this. Can I run a query that will identify all orders records which do not have a claim record attached.

Many thanks for all your help.

 

[fneily]

From my transaction table, you can get 1)how many forms were sent to a client, 2)through an unmatch query how many were not returned.

Looking at LesPaul's tables, it seems you need to count how many clients returned a form then subtract that number from Number of Forms in the first table.

So, you don't care what forms were returned/not returned, you just care how many?

 

[PHV]

Have you tried to follow the unmatched query wizard ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[ProjectExplorer]

Thanks everyone - the unmatched query wizard does the trick. I didn't want to go any further unless this was possible.