G
Guest
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 are: 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
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 are: 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