How can I do this?!?!?! 1

[WestView]

I need to create a QA report to check for data entry errors.
I have a seemly simple 3 table report: MEMBER, SUBSCRIPTION and DETAIL.

The MEMBER table holds member info, the SUBSCRIPTION table holds the subscription info for the member and the DETAIL table holds info on the member’s subscription level.

The MEMBER and SUBSCRIPTION tables have a typical one-to-many relationship. The MEMBER and DETAIL tables have a one-to-one relationship

The MEMBER table has a field called ‘Type’ that indicates the member’s category. Members are either Individuals (IN), Companies (CO) or Branches (BR).

A company branch (BR) may purchase a subscription to one of our products. Individuals (IN) who work for that branch may be granted access to that (on-line) subscription. If they are, it is indicated in the DETAIL table where the branch (BR) ID is entered --- this is the MEMBER.ID for a BR member category. For example, if ACME Company has a branch, “ACME New York” (ID #1234) that purchased an on-line subscription. “ACME New York” then granted access to this product to 5 of it’s employees. Therefore if Jane Doe (ID #5678) is and employee of “ACME New York” and has been granted access to this product then she would have #1234 populated in the DETAIL.Branch_ID field of her matching record giving her access to this product. ONLY branches (BR) can do this!

I need to create a report that checks to see if any of the numbers entered in the DETAIL.Branch_ID field is correct. For instance, if the numbered entered is 5555, which the NAME.ID indicates is a CO record - not a BR record – then that should be in this report.

I think that this sort of thing requires table aliasing or a SQL Expression because it seems to me I would need to requery the NAME table.

Any/All help in this would be greatly appreciated!!!

Thanks,

Tom

 

[MrBillSC]

If I understand you correctly:
Table Members are type "IN", "CO", "BR" and contain a Member_ID field.

Table Detail links 1 to 1 with table Members and contains a Member_ID field and a Branch_ID field. The Branch_ID field will only contain a value for "IN" type members. The Branch_ID field contains the Member_ID value for a "BR" type Members table record.

You want to find Detail table records with Branch_ID values that link to Member table records that are not type "BR".

Hard to explain, but if the above is correct:
Equal join Members.Member_ID to Detail.Member_ID.
Equal join Detail.Branch_ID to Members_Br.Member_ID (alias of Members table).

In your record select formula:
Members.Type = "IN" and
Members_Br.Type in ["IN","CO"]

Is this close to what you want?

MrBill

 

[WestView]

MrBill,

Wow! Not only was your response quick it was spot on! I thought it was a rather complex problem/description, but you analyzed it exactly. Well done!

Can't thank you enough!!!

Tom