A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Need help linking 2 tables for entering new & editing existing rec



 
 
Thread Tools Display Modes
  #1  
Old January 12th, 2005, 04:53 PM
scott
external usenet poster
 
Posts: n/a
Default Need help linking 2 tables for entering new & editing existing rec

greetings.... i apologize up front as this seems like an "easy to solve"
issue but i haven't used access for a long time and it appears that
everything i thought i knew has been purged from my feeble memory..... i
will state the objective and provide details of current tables, query, data
access sheet so hopefully this won't inundate you with info....

the goal is to have two tables; one for vendor information (vendorinfo) such
as name, business type, address, email, etc. and one to capture info about
an annual event (annualevent) that the vendors in the vendorinfo table may or
may not attend each year. i need to be able to input new vendors into the
vendorinfo table and sometimes the annualevent table at the same time. we
will encounter situations whereby:
1) we are entering data about a new vendor and only the vendorinfo table
will be used.
2) we are entering vendorinfo for the first time and at the same time we
are entering information about the event they are attending (for instance....
new vendor who is not in our vendorinfo table sends us an event application,
we mark them paid and assign a booth number (AmtPaid and AssignedBooth are in
the annualevent table).
3) also have a need to edit data when there is a vendor in the vendorinfo
table but has never attended an event so s/he is not in the annualevent
table. the edit should allow updating all fields from both tables at the
same time.

in fact this is the ultimate desire - to be able to enter new or edit all
fields from both tables at the same time.


details:

tables:

vendorinfo - info such as vendor owner name, business name, business type,
address, email, etc. primary key is autonumber field called ID. had to use
this because we occassionally encounter vendor owner AND business names which
are the same so there is no way to key these fields.

annualevent - no key in this table. fields a ID (duplicates Ok),
EventYear, AppRcvd, AmtPaid, AssignedBooth, and may eventually include others
but not relevant to this discussion for now.

relationships:
one-to-many relationship created between these two tables keyed on field=ID.
one record with no dups should exist in the vendorinfo table and zero to
many records can exist in the annualevent table.

current issue with the EditVendor query:
the query "findvendor" works fine when only using the vendorinfo table. it
uses wildcard matching on owner name and business name so i can pull multiple
records in case they are very similar in spelling. the problem occurs when i
add the annualevent table to the query. because this is the first time we
are capturing the data the annualevent table has no records in it. in fact
the vendorinfo table contains all POTENTIAL vendors that we want to contact
each year in case they want to attend our event. this means that there will
not always be a matching record for every vendor in the annualevent table.

this causes the query to fail because there is not a record in annualevent
with a matching ID to the vendorinfo table.

data access page:

i have one data access page that works fine when only entering data into the
vendorinfo table. similar to the above query this page no longer functions
when i add the annualevent table and attempt to enter data into both tables
during the same data entry task. if possible i need to be able to have one
data access page that i can use to enter data into as many fields as
necessary from one or both tables.

one last question but certainly not a priority....it seems that eons ago in
a different version of access that i was able to include the primary key from
vendorinfo (called ID) in the annualevent table as a foreign key so that they
were forever and ever linked. from the help articles this appears to be
possible in a access project (.adp) but not .mdb. is there a similar process
now or am i way off base?

i hope i have supplied enough info to at least start a dialogue.

thanks in advance!
sj
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
linking tables in a query - can not see the fields in a form King Kaos New Users 0 January 11th, 2005 03:17 AM
Linking to tables, Can't see all tables for selection K Connor General Discussions 0 August 31st, 2004 06:57 PM
Question about linking tables Heather Database Design 2 August 10th, 2004 07:21 PM
Help Please Copying or Linking Tables A2002 Mike Wilson New Users 2 June 5th, 2004 01:54 PM
Is there any method of editing existing tabstops? Word 2K JethroUK© New Users 3 April 29th, 2004 04:05 PM


All times are GMT +1. The time now is 10:15 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.