Linking forms/tables together?

[PhilD]

Hi squid..

lol lol, pan fried brains fried in beer!

Lets totally ignore customers and its terms and use SHOP and OWNER.


lets start again.

I want a pop up form to appear on the main form which brings up the owners details. (Contact name, address and tel number.)

Now i'm not sure whether i will need one table or two tables for this.

The database is for keeping track of Shops who take credit card debit cards and switch payments at their shop
Each shop Will have just one unique shop number.

On the main table all details about the shop are entered such as Payment form, number of terminals and % rate they pay on credit cards.

I have another table called owners.

This table will contain all the owners contact details.

Each shop will have a owner and only one owner.
Each owner will also have one shop.


I want to know whether i am best creating one table or two.

really really hope i'm getting cleared at this..

 

[Squid11]

Got It

Thats a bit clearer. I'm no pro at this, but I think I've got your answer.

Short story is as posted by others earlier in the thread....You don't need another table because your relationship of owners to shops is 1 to 1. You also DO NOT need additional tables to use different forms to control related data in the same record.

I cooked up the attached example for you. All of the shop and owner details are in the same table. I "assume" that each shop has many transactions that you may want to keep track of, so I linked a transaction and customer table as an example.

But the heart of your question is in the forms. The ShopInfo form has command buttons on it that open up another form with the OwnerInfo from within the same table. If you follow your nose through the command button toolbar when you build a form it will ask you if you want to look up (filter) certain records when you use the "open form" command button. That's all I did, so that it opens up the owner info on the record that you have open by matching the ShopName Field on both. If you use the unfiltered, it opens up the ShopOwners data to the first record, which may not be what you want.

The area I'm not good at here is refreshing the data. When you have 2 open forms that affect the same data you need to "refresh" the data as you get into the second form, or it won't have the data you just put in. I put in refresh buttons on the forms to force this, but I'm sure there is a way to program it or automate it, if you want to go Gucci. Mash the refresh before you open the second form.... If you site search for "refresh" there's probably an answer out there somewhere.

Bottom line, keep all of your data in 1 table if it is truly 1-to-1. Now as soon as one of your owners opens up a second shop....you're screwed from a pure database construction standpoint. IF that may happen, I would set up 2 tables with a one to many from owners to shops....just in case....

Good luck,
Squid

 

[PhilD]

Squid and everyone else.. you're all genius, take a bow..

It actually works EXACTLY like i want it. In the end thanks to all the advice i stuck to one table and two forms. then linked them together. Feel a bit of a dummy now as it was sooooo simle..

Thankyou VERY much to eveyone who helped.

 

[neileg]

OK, just got back to the office and logged in. Happy new year!

I'm pleased that you're pleased, Phil. Well done, Squid.

Trouble is, I think you need two tables, one with owners details and therefore the merchant number, etc. The second should contain the shop details, since you said each owner could have multiple shops...