Relationship Woes with Specific Query (1 Viewer)

[Junkee Brewster]

I have a pretty standard relationship set up, with the following tables:

Customers: A row/Cust ID for each customer
WorkOrders: each customer can have multiple work orders (linked to Customers by CustID, individual ID is WOID)
Jobs: each work order can have multiple job records attached (linked to WorkOrders by WOID, individual ID is JobID).

As far as I can tell, they're fine and all other forms etc work, updating no problem, referrential identity is enforced etc...

Problems: When I go to make a "job allocation" query for subsequent form, I select: Customers, Work Orders and Jobs table (I then filter by location, but
this problem remains unfiltered also). They link up fine. The query isn't nonsensical either - it lets me add new data. BUT instead of showing all the customers and work orders for the location due for the location, it will ONLY display the records that have details in the Jobs table (the lowest in the relationship chain). Basically, If the jobs sections are empty or "unattempted", then NO details AT ALL will show up in the query. Which is a problem, because I want to see ALL the "unattempted" jobs in the area to allocate them, including customer data and work order numbers. I also need to have the jobs table present, so I can allocate a job date, a contractor etc.

I'm not sure what to do. As far as I can see, the set up I have IS very standard tables/relationships-wise. I have to work it out, otherwise I'm going to have to revert to some kind of 'super-table' (like in our old system) - which I am at loathe to do - I don't want to merge Work Orders and Jobs because that means a lot more typing for me.

Would cascade update in the relationships do anything? (it is checked).

I've been away from Access for awhile and I'm just drawing a blank on this one. Any help greatly appreciated. Thank you.

 

[Len Boorman]

The query that you are using will use the relationships as defined in the relationship diagram. This ususlly is okay but I think that you have a circumstance here where you need to amend the relationship in the query ONLY.

select the relationship line in the query going to the Jobs(I think) table, Right Click and you can then select Join Properties.

Now you can (slightly) modify the relationship to include all rows from Table X and only those that match from Table Y instead on the "Equi Join" you get by default.

If you look at the sql after you have made this change you will will see that the join type has been modified.

Your ER Diagram however remains unchanged

Len

 

[Junkee Brewster]

Len,

Beautiful. Works a treat. I had slowly been progressing along those lines all evening (after I posted) but was still a bit lost/rusty and feeling every-so-slightly teary. Forest from trees and all that.

Thanks very much and have a smashing weekend.