confusing criteria

[Guest]

I have a table that is storing information from electrical panels. In my
table there is a column for circuit phase and another for actual amp load, in
the fields under the phase column the entries are either A, B, C, AB, BC, CA
or ABC depending on how many poles and where in the panel the individual
circuit lies. Where I am having trouble is in my query there are three
different amperage fields; one each for A Phase, B Phase and C Phase, if an
item is listed in table as only A phase then it should only show actual amp
load in the A phase of the query and so forth for B and C. if an item shows
to be on BC phase in the table then the entry for actual amp load should show
up in both the B and C phase columns in the query. I have tried If
statements and Like with wildcrads with no luck getting the correct amperage
readings into the correct columns in the query I've gottem either no records
at all or all table entries into all query fields regardless of phasing. any
help would be much appreciated.

 

[Allen Browne]

One circuit can have many phases (up to 3.)
The 3 phases can turn up in many different circuits.
There is therefore a many-to-many relation between circuits and phases.

Try these 3 tables:

Phase table (3 records):
PhaseID primary key (Can be text, i.e. record each for A, B, and
C.)
Ampere Number Max Amps on this phase.

Circuit table (one record for each circuit):
CircuitID primary key

CircuitPhase table:
CircuitID Relates to Circuit.CircuitID
PhaseID Relates to Phase.PhaseID.
So, if circuit 29 has 3 phases, there will be 3 records in this table: a
29-A, 29-B, and 28-C.

Now you can create a query using all 3 tables.
Depress the Total button on the toolbar in query design.
(Access adds a Total row to the design grid.)
Group by CircuitID, and sum the amperage to get the max possible amps for
each circuit.

The 3rd table above is known as a junction table. This is the standard way
to resolve a many-to-may relation into a pair of one-to-many relationships.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

message
news:[email protected]...

 

[Guest]

Allen,

Thank you for your response, I do have one question though. Does
implementing the three tables give me a more normalized structure and save
from pain and heartache down the road or just give me a way around the many
to many situation?

just before I read your response I did some tinkering with the query and
came up with

SELECT [CPC Information].building, [CPC Information].CPC, IIf([CPC
Information]![phase] Like "*A*",[CPC Information]![actualamps],0) AS [A Phase
Amps], IIf([CPC Information]![phase] Like "*B*",[CPC
Information]![actualamps],0) AS [B Phase Amps], IIf([CPC Information]![phase]
Like "*C*",[CPC Information]![actualamps],0) AS [C Phase Amps], Round((((([A
Phase Amps]+[B Phase Amps]+[C Phase Amps])/3)*120)*1.732)/1000,3) AS KVA
FROM [CPC Information];

which worked, but if it means saving from trouble down the road I will go
ahead and try the three tables.

 

[Allen Browne]

The general structure I suggest is the normalized solution, and will be much
easier to query in the long run.

The specifics may vary, according to your needs.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

message

Allen,

Thank you for your response, I do have one question though. Does
implementing the three tables give me a more normalized structure and save
from pain and heartache down the road or just give me a way around the
many
to many situation?

just before I read your response I did some tinkering with the query and
came up with

SELECT [CPC Information].building, [CPC Information].CPC, IIf([CPC
Information]![phase] Like "*A*",[CPC Information]![actualamps],0) AS [A
Phase
Amps], IIf([CPC Information]![phase] Like "*B*",[CPC
Information]![actualamps],0) AS [B Phase Amps], IIf([CPC
Information]![phase]
Like "*C*",[CPC Information]![actualamps],0) AS [C Phase Amps],
Round((((([A
Phase Amps]+[B Phase Amps]+[C Phase Amps])/3)*120)*1.732)/1000,3) AS KVA
FROM [CPC Information];

which worked, but if it means saving from trouble down the road I will go
ahead and try the three tables.

One circuit can have many phases (up to 3.)
The 3 phases can turn up in many different circuits.
There is therefore a many-to-many relation between circuits and phases.

Try these 3 tables:

Phase table (3 records):
PhaseID primary key (Can be text, i.e. record each for A, B,
and
C.)
Ampere Number Max Amps on this phase.

Circuit table (one record for each circuit):
CircuitID primary key

CircuitPhase table:
CircuitID Relates to Circuit.CircuitID
PhaseID Relates to Phase.PhaseID.
So, if circuit 29 has 3 phases, there will be 3 records in this table: a
29-A, 29-B, and 28-C.

Now you can create a query using all 3 tables.
Depress the Total button on the toolbar in query design.
(Access adds a Total row to the design grid.)
Group by CircuitID, and sum the amperage to get the max possible amps for
each circuit.

The 3rd table above is known as a junction table. This is the standard
way
to resolve a many-to-may relation into a pair of one-to-many
relationships.

message