PK or not using 3 tables

[avalon60]

I have built a small but useful, to me, database to log our utility bills etc.

I made 3 tables:
tblBlances
tblGas
tblElec

In each table I have ID as autonumber and made it a PK, and also linked the tblGas and tblElec to tblBalances in the Relationships window.

I have one form which shows all the information for each record from the relevant query for each table.

Now for some reason I cannot add a new record in the form, and it transpires that it seems to be down to having the 3 PK's. So I removed the PK from tblElec, and now I can add a new record.

I would have thought that each table has to have a PK, but as a relative newbie with this, I am now not so sure.

Can some one please explain why the above situation occurred and why I seemed to have cured it.

Thanks

 

[Alansidman]

What are the fields in your tables? To join the tables, they must also have foreign keys.

 

[avalon60]

These are the 3 tables and field names:

I'm not sure where or how I would assign a foreign key to join the tables

tblBalances
UptoDate
PaymentAdjust
UsageCost
Discount
Vat
NETCost
AccountBalance
LastBalance
BalanceID PK Autonumber

tblElec
UptoDate
NormKWH
NormKWHCost
CostNorm
XtraKWHCost
TotalCost
ElecID Autonumber

tblGas
UptoDate
NormUnits
NormUnitCost
CostNormUnit
XtraUnits
XtraUnitCost
TotalCost
GasID PK Autonumber

 

[Pat Hartman]

The query wasn't updateable because it contained two separate 1-1 relationships.
balances --> Elec
and
balances --> Gas

If Gas and Elec are billed together, you can put them in the same table. The balances table looks like it should be the account table and contain the subscriber's name and address.

 

[avalon60]

The reason I had Gas and Elec in 2 separate table was because I chose to see them as 2 different products. Now I have now put both Gas and Elec in the same table with ID set as PK in both tables: tblBalances and tblUtilities.

Now I will have further questions on queries and forms for this database, so should I ask those questions in the relevant sections of the forums.

Thanks

 

[Pat Hartman]

If you want to see them as two different products, use two subforms on the main form. The main form will be bound to a query of balances. and the subforms will be bound to queries of gas and electric respectively.

 

[avalon60]

I did try that to see how it looked and there 3 sets of navigation buttons to go through or to a record now, 1 for the main form and 1 each for the 2 subforms.

I suppose it is a matter of choice here to see what looks and works best.

 

[Pat Hartman]

You can turn off the navigation buttons for the subforms and only use the scroll bars as long as there aren't too many rows. If you are using A2007 or A2010, you can give each navigation bar a name so they are less confusing.

 

[avalon60]

I have text boxes and labels for each item?? , which are stacked one above the other on the subforms, and I when I turned off the navigation buttons (on the bottom of the form), I could not move to the next record.

I must have done something wrong there??

 

[Pat Hartman]

You have to set the scroll bars to yes to have a virtical scroll bar.

 

[avalon60]

I think I'll stick with the 3 navigation controls on the bottom of each form.

Thanks