Go Back   Access World Forums > Microsoft Access Discussion > Theory and practice of database design

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 01-04-2012, 12:25 PM   #1
masoud_sedighy
Newly Registered User
 
Join Date: Dec 2011
Posts: 122
Thanks: 57
Thanked 1 Time in 1 Post
masoud_sedighy is on a distinguished road
problem in inserting records in new design of my database

Hello
In previous design of my db as I describe like below I was running 3 append queries regularly (by 1 macro) for inserting records in my tables after extracting data by queries (3 simplequery) from 1 base table that comes from imported excel file in my database.
Now I have changed design of my tables to the new ones like below and I wanted to use auto number fields as primary and foreign key of tables. Now I do not know how I have to make my append queries from base table and inserting records to these 3 new tables. Specially for table 3 (new) because I do not know docid,tranid?
Please help.
Best regards.
I have 3 tables.
Table 1:
Doc no (Primary Key) text (50)
Title text (100)
Originator text
-------------------------------------------------
Table 2:
Trans no (Primary Key) text (15)
Date date
----------------------------------------------
Table 3:
Doc no (Foreign key) text (50)
Trans no (Foreign key) text (15)
Size text

Base table (import from excel file)
Doc no
Trans no
Title
Originator
Date
------------------------------------------
New design
Table 1 (new):
Doc id (Primary Key) auto number
Doc no text (50) required (yes) no duplicate
Title text (100)
Originator text
--------------------------------------------------------
Table 2 (new):
Transid (Primary Key) auto number
Trans no text (15) required (yes) no duplicate
Date date
------------------------------------------------------------
Table 3 (new):
Doc id (Foreign key) auto number required (yes)
Trans id (Foreign key) auto number required (yes)
Doc no text(50) required (yes)
Trans no text (15) required (yes)

masoud_sedighy is offline   Reply With Quote
Old 01-06-2012, 09:16 PM   #2
GinaWhipp
AWF VIP
 
GinaWhipp's Avatar
 
Join Date: Jun 2011
Location: Ohio, USA
Posts: 5,349
Thanks: 20
Thanked 892 Times in 877 Posts
GinaWhipp has a spectacular aura about GinaWhipp has a spectacular aura about
Re: problem in inserting records in new design of my database

Before any queries, how are you going to relate these tables? Those FK's in Table 3 won't be updated as an AutoNumber... they would need to be Number (Long) or are you not creating Relationships? And why are you duplicating...

Quote:
Doc no text(50) required (yes)
Trans no text (15) required (yes)
...in Table 3 when you can just pull those values via a query when you need them.
__________________
Gina Whipp
Microsoft MVP (Access 2010-2015)


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
GinaWhipp is offline   Reply With Quote
Reply

Tags
import

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Question Database design for employee records g.comley General 0 08-02-2009 01:20 PM
Database Design problem Kurth General 11 08-21-2008 07:50 PM
any problem if i design the database in 2003 and use in 2000 bigbigi General 1 08-20-2007 01:19 AM
Non-uniqueness problem - Please comment on my ENERGY database design Alexsasha Theory and practice of database design 5 11-14-2006 04:29 AM
Access Database Design Problem. NewSkool General 10 07-12-2006 04:43 AM




All times are GMT -8. The time now is 01:35 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World