Displaying all comapany branches

[Stewart Allen]

Hi there,

I'm trying to create a query that will display all branches in a company
even if that branch hasn't made a claim. The main manufacturing company
makes the machines and distributes the machines to the other branches and it
is up to each branch to sell the machines to the customers. It is also the
branches responsibility to make sure the machine is registered when sold and
to deal with any warranty claims. The branch then sends all registration
details and warranty claims back to the manufacture where the manufacture
processes them.

On a monthly basis the manufacture creates reports and send them back to the
branch notifying them of the status of each claim submitted: credited, on
hold or declined. I've created this report in 2 ways, one as a standard
Access report and the other in Excel. It's the one created in Excel that
they use because it can be emailed easily and each branch manager can sort
the information to their own liking.

Not every branch sends in warranty claims but the manufacture wants the
reports to display all the branches even if they haven't made a claim, at
the moment they only display the branches that have made claims.

*tblBranch*
BranchID (PK) Autonumber
Branch

*tblMachines*
MachineID (PK) Autonumber
S/N
etc

*tblRegistrationDetails*
RegID (PK) Autonumber
MachineID (FK) one to one from tblMachines
BranchID (FK) one branch can register many machines
etc

*tblWarrantyClaims*
WarrID (PK) Autonumber
RegID (FK) one registration can have many claims
ClaimNo
ClaimDate
Status (FK) from tblstatus
etc

The overall table structure is a bit more complicated than this but how can
I display all branches in the reports even if the branch hasn't made a
claim? Report must show S/N, ClaimNo, Branch, ClaimDate, Status plus a few
more items from a WarrantyDetails table.

TIA
Stewart

[Todd Shillam]

Stewart,

It appears like you need to use a left or right outer join in your report
query. If you use a left outer join, the query will display all records in
the left table, even those without any related records in the right
table(s).

Best regards,

Todd

"Stewart Allen" <sa****@ThisPartNotVailid.wave.co.nz> wrote in message
news:bq**********@news.wave.co.nz...
Hi there,

I'm trying to create a query that will display all branches in a company
even if that branch hasn't made a claim. The main manufacturing company
makes the machines and distributes the machines to the other branches and it
is up to each branch to sell the machines to the customers. It is also the
branches responsibility to make sure the machine is registered when sold and
to deal with any warranty claims. The branch then sends all registration
details and warranty claims back to the manufacture where the manufacture
processes them.

On a monthly basis the manufacture creates reports and send them back to the
branch notifying them of the status of each claim submitted: credited, on
hold or declined. I've created this report in 2 ways, one as a standard
Access report and the other in Excel. It's the one created in Excel that
they use because it can be emailed easily and each branch manager can sort
the information to their own liking.

Not every branch sends in warranty claims but the manufacture wants the
reports to display all the branches even if they haven't made a claim, at
the moment they only display the branches that have made claims.

*tblBranch*
BranchID (PK) Autonumber
Branch

*tblMachines*
MachineID (PK) Autonumber
S/N
etc

*tblRegistrationDetails*
RegID (PK) Autonumber
MachineID (FK) one to one from tblMachines
BranchID (FK) one branch can register many machines
etc

*tblWarrantyClaims*
WarrID (PK) Autonumber
RegID (FK) one registration can have many claims
ClaimNo
ClaimDate
Status (FK) from tblstatus
etc

The overall table structure is a bit more complicated than this but how can
I display all branches in the reports even if the branch hasn't made a
claim? Report must show S/N, ClaimNo, Branch, ClaimDate, Status plus a few
more items from a WarrantyDetails table.

TIA
Stewart

[Stewart Allen]

Thanks Todd but it did not work, get ambiguous joins error message for both
the left outer and right outer joins. I tried all possible combinations of
right and left with all the tables.

Just a little correction to the table structure I'd posted it should be:
*tblBranch*
BranchID (PK) Autonumber
Branch

*tblMachines*
MachineID (PK) Autonumber
BranchID (FK) one branch can have many machines
S/N
etc

*tblRegistrationDetails*
RegID (PK) Autonumber
MachineID (FK) one to one from tblMachines
etc

*tblWarrantyClaims*
WarrID (PK) Autonumber
RegID (FK) one registration can have many claims
ClaimNo
ClaimDate
Status (FK) from tblstatus
etc

The change is the tblMachines has the BranchID so the manufacture can trace
who has been sent the machine even the machine hasn't been sold to a
customer. In me previous post I had the BranchID in the
tblRegistrationDetails.

Although my client has said he'd like all the branches to appear in the
reports but it won't be the end of the world if it doesn't happen. Situation
required; One branch can send in many warranty claims per month and each
warranty claim can only belong to one branch but not every branch sends in
claims every month. The reports are filtered via the claim date field to
find only the claims for that month. There is another table,
tblWarrantyDetails (parts, quantity, cost), as well but I can add that later
once I can get this part working first.

Does anybody have any other sugestions here?

Stewart
"Todd Shillam" <ts******@hotmail.com> wrote in message
news:xM********************@centurytel.net...

Stewart,

It appears like you need to use a left or right outer join in your report
query. If you use a left outer join, the query will display all records in the left table, even those without any related records in the right
table(s).

Best regards,

Todd

[Pieter Linden]

Sounds like you're trying to use two outer joins in the same query in
some way that the query interpreter doesn't like. You could probably
do this if you break this into separate queries.

Something like:
(1) find all the Warranty Claims for each Branch (Showing all
Branches, regardless of whether they have claims)

SELECT ...
FROM tblBranch LEFT JOIN tblRegistrationDetails AS RD ON
tblBranch.BranchID = RD.BranchID...

And then use that query in the second query and you should be good to
go. You can't do something like

TableA---LeftJoin---TableB--RightJoin---TableC

that's like a full outer join, which Oracle can do, but Access can't.