MS-Access: two tables to one form

I am making a simple Access database. At the moment it mainly consists of a "Client" table (with details about each person, regarding name, phone number, address etc), and a second "Marketing" table (with marketing fields like "How did you hear about us? ", customer type etc), which is a questionairre of one-to-one relationship for each client.

I guess I could slap everything on one table, given the one-to-one relatoionship, except that I figured it would be tidier to havethe "marketing" aspects separate. If everything was on one table, the table could grow huge.

Okay. Here's he question:

If I make a "new client" or "client details" form, it is easy to add fields from the field list (floating box where elements can be dragged onto my main form). But now I want to add a "how did you hear about us?" field to my "Add new client" form. How can I add this field, in an editable state, when it comes from the "marketing" table?

I'm new to Access and am unsure whether the best direction would be to add a subtable (works, but there are annoying "next box" arrows at the bottom of the subtable), create a major "combine everything" table and work from there, or whatever. surely there is a way of adding just a text box and somehow renaming its properties to point to the [Marketing]![how_did_you_hear] value?

Bear in mind that I want the choice to be editable from the "new client" form (not read only) and also from a form I wish to make later, specifically looking at marketing details.

Thanks in advance - Lancer.

P.S: Windows-98SE with MS-Access from MS-Office 2000

 

You need to think about how these tables are going to relate to each other. Typically if you are going to have a 1 to 1 relationship you should have all the information contained in the same table. Possible exceptions would be if you wanted to keep some information separate for security reasons (ie to split out employee social security numbers from the rest of their information).

What is your primary key going to be for the two different tables? What field will link the two tables?
My suggestion would be to keep the information in one table. When I first started designing DBs (I was self taught), I wanted to split everything up into smaller pieces that (I thougt) made sense. After 2 major redesigns of my primary DB I found that bigger tables are usually better. You can use querries to parse out the information you want later.

Don't worry about your tables getting too big. I have one table that has 41 different fields. I use 5 different tables to add information to it. My archive database has a table in it with over 68,000 entries.

Also I would suggest buying: Running Microsoft Access 2000 by John Viescas from Microsoft Press. I think I got my copy off Amazon.

 

Thanks for your reply (I was hoping someone would post back)

My database is starting to look quite good now that I've taken your advice and just used one big table.

However, if anyone can tell me how to make two separate tables feed into one form, I'd still be interested.

 

You can either create a form query (under Properties, Record source, click the three little dots), or you can make a subform of the main form with a parent child join. Don't worry that you get the annoying "Next record" stuff, you can get rid of that.
Create the subform to look how you want it. Right-click the little black box in the extreme upper left of the form, got to Properties. "Use Navigation buttons" change to "No," "Use Record Selectors" change to "No." I do this a lot and get a nice flowing form. Message me if this isn't clear. - FreewareGuy

 

Thanks

Tried the first method - worked very well.

Will soon look at the second. I has this kind of working as a subform before, though you have now shown me how to get rid of the annoying "next person" borders on the subform.

Cheers.