Creating Multiple One-to-Many Relationships is creating Chaos!

[kellem80]

I have a table, "Table A" created with basic data. I have another table, "Table B" that houses certain transactions done by Table A. However, I also need another table, "Table C", that houses a different set of transactions for the Table A data (I will actually have~4 tables of different transaction types).
I have successfully created the relationships necessary (I believe) between A & B. Whenever I add C or any others, I run into problems, WHY???

I may not be setting this up correctly, but basically the combination of the elements in Table A make a unique record that must have different transaction types associated with it, but must also be able to be summed, grouped and sorted by the each of the elements (example: run query to sum budget by Company or by Group), but also tied together for the unique record.

Table A Fields:
AID------Autonumber (Primary Key)
Company-Text
Group----Text
Project---Text
Task-----Text

Table B Fields:
BID------------Autonumber (Primary Key)
AID-----------Number (lookup & Relationship built between A&B)
Budget-------Currency
ChangeDate--Date/Time
DocNumber---Text

Table C Fields:
CID------------Autonumber (Primary Key)
AID------------Number (lookup)
Cost-----------Currency
EnterDate------Date/Time
DocNumber-----Text
POPStart-------Date/Time
POPEnd---------Date/Time


Table D Fields:
DID------------Autonumber (Primary Key)
AID------------Number (lookup)
Cost-----------Currency
Amount--------Date/Time
DocNumber-----Text
Description-----Memo
POPStart-------Date/Time
POPEnd---------Date/Time



Any insight is appreciated.
thx

 

[Montez659]

kellem80 - you haven't really defined what problems you are running into. Also, the title of your posts suggest that Table A has a one to many relationship with Tables B, C, and D - is that correct? Considering that Table C & D are the exact same structure, is there a reason that they are separate? Why not combine them into one table adding an additional data item to show the difference between the two types of records. You can then use queries to separate them out.

Anyways, if you could post what types of problems you are having, I will help if I can.

 

[kellem80]

Montez659,
Thanks. I think I figured out my problem with this issue. In the end the structures for B,C & D would be very different, this was just initial setup, I know for future integration the best set up will be separate tables.
Thnaks, again.