There's no such thing as a stupid question, but they're the easiest to answer.


Business Applications Business Applications
Search Search
Search for:
Tech Support Guy > > >

Creating Access database with multiple tables for query, forms


NAWCC_Mart's Avatar
NAWCC_Mart NAWCC_Mart is offline
Computer Specs
Junior Member with 3 posts.
Join Date: Nov 2010
Experience: Beginner
01-Dec-2010, 12:27 PM #1
Question Creating Access database with multiple tables for query, forms
Good morning!

Here are my computer specifications:
OS Version: Microsoft Windows XP Professional, Service Pack 3, 32 bit
Processor: AMD Athlon(tm) II X2 250 Processor, x86 Family 16 Model 6 Stepping 2
Processor Count: 2
RAM: 3327 Mb
Graphics Card: NVIDIA GeForce 6150SE nForce 430, 512 Mb
Hard Drives: C: Total - 472278 MB, Free - 447618 MB; D: Total - 4649 MB, Free - 421 MB;
Motherboard: PEGATRON CORPORATION, 2A99h, 6.01, X312345678
Antivirus: eTrust ITM, Updated: Yes, On-Demand Scanner: Enabled

Here are my questions:
1) I am trying to set up an Access database that takes information from 3 tables (Customer Info, Ad Data, Issue Dates) and makes one form with all information included.
(I have a "Complete" table with the Customer Info, Ad Data, and Issue dates all included that works well with the form. The problem with this is that this will easily become a huge, unmanageable database.)
2) I want to be able to update the form (which also updates the tables) as well as being able to pull up past records. (I don't want to use a subform, because the Ad Data are all calculated fields.)
3) I also want to be able to group each ad by issue date.
4) The Customer Info table should be the master table. When I try to run a query using Customer Info, Ad Data, and Issue Dates as a record source, I get a blank query (not populated with data).
5) I am not able to create relationships between the Customer Info, Ad Data, and Issue Date tables for the query. (I want to have a one-to-many relationship; 1 customer, many ads). Access won't let me create a Primary Key for the Ad Data table. When I try to use "Member Number" as my Primary Key I get the following error message: "The changes you requested to the table were not successful as they would create duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again". When I change the "Indexed" field to read "Yes (duplicates okay)" or "No" it saves the change, but inserting the Primary Key makes it default back to "Yes (no duplicates).

I apologize if this is not clear, I tried to make it as concise as possible. I have been working on this piecemeal (between issue deadlines) since July. . .I have read the other threads on Access databases, tables, relationships, etc. on this forum, but am completely confused when I try to apply it to my own database, because it still doesn't work.

My boss would like this ready to run by December 15th, so I'm under the wire here. Any help would be greatly appreciated!

Thank you!
NAWCC_Mart's Avatar
NAWCC_Mart NAWCC_Mart is offline
Computer Specs
Junior Member with 3 posts.
Join Date: Nov 2010
Experience: Beginner
01-Dec-2010, 12:29 PM #2
sorry, forgot to add I'm working in Access 2003
OBP's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,825 posts.
Join Date: Mar 2005
Location: UK
01-Dec-2010, 12:39 PM #3
NAWCC_Mart, welcome to the Forum.
It sounds like you are making a common mistake where Master and Subtables are concerned.
The Linking field in the Subtable should NOT be an Autonumber or even a Key field. It should be a Field Type Number -Long and should store the Autonumber Key field Value from the Master Table.
Take a quick look at some of the Databases that I have worked with on the forum to see what I mean.
The Master Table can't have any Duplicates in the Key Field, so does your Member Number field in the Customer Table have any Duplicates?
I do not give up easily
NAWCC_Mart's Avatar
NAWCC_Mart NAWCC_Mart is offline
Computer Specs
Junior Member with 3 posts.
Join Date: Nov 2010
Experience: Beginner
15-Dec-2010, 01:15 PM #4
OBP, thank you so much for your quick reply! I am sorry it has taken me so long to answer you - other projects came up at work, and now we're heading into our deadline period. I hope to be able to get back to this in January 2011. Thank you again, and have a Merry Christmas and a Happy New Year!
As Seen On

BBC, Reader's Digest, PC Magazine, Today Show, Money Magazine

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

access database, forms, primary key, queries, query

Are you having the same problem? We have volunteers ready to answer your question, but first you'll have to join for free. Need help getting started? Check out our Welcome Guide.

Search Tech Support Guy

Find the solution to your
computer problem!

Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
Thread Tools

Similar Threads
Title Thread Starter Forum Replies Last Post
Solved: Create a SUM when there is no data IndyBrown Business Applications 12 24-Aug-2010 03:59 PM
search one table to populate other tables in a form profdev Business Applications 8 29-May-2009 10:46 AM
How to install Ms.Access Database on website? ipmart_computers Business Applications 6 10-Nov-2008 07:46 PM
Copy of Access database and linked tables Paintergrrl73 Business Applications 1 22-Oct-2008 02:44 PM
Solved: My form doesn't fill in my table????? thewebbmasterone Business Applications 4 04-Aug-2008 09:14 AM

You Are Using: Server ID
Trusted Website Back to the Top ↑