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  

Updating two identical formatted tables with one form



 
 
Thread Tools Display Modes
  #1  
Old January 7th, 2010, 04:28 PM posted to microsoft.public.access.tablesdbdesign
Cynw
external usenet poster
 
Posts: 2
Default Updating two identical formatted tables with one form

At present I have a membership database with all members held in one table. I
have been asked to split this table into two tables - family and individual
members. The user still wants to view all the records in both tables using
the same form as at present. However if I use a UNION query on the two tables
and link the form to this query, I have found that although I can view all
the records from both tables, I now cannot update them.
Any advice on a better way to design and link the tables would be appreciated.
  #2  
Old January 7th, 2010, 04:53 PM posted to microsoft.public.access.tablesdbdesign
Gina Whipp
external usenet poster
 
Posts: 3,500
Default Updating two identical formatted tables with one form

Cynw,

Let's start from the beginning why did *whoever* think families should be
segregated from individual member? My better way is put them back into one
table and use a field to indicate if it is an individual. Any if *whoever*
wants to see all the individuals you can run a query based on the before
mentioned field.

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

"Cynw" wrote in message
...
At present I have a membership database with all members held in one
table. I
have been asked to split this table into two tables - family and
individual
members. The user still wants to view all the records in both tables using
the same form as at present. However if I use a UNION query on the two
tables
and link the form to this query, I have found that although I can view all
the records from both tables, I now cannot update them.
Any advice on a better way to design and link the tables would be
appreciated.



  #3  
Old January 8th, 2010, 11:30 AM posted to microsoft.public.access.tablesdbdesign
Cynw
external usenet poster
 
Posts: 2
Default Updating two identical formatted tables with one form

The individual members already have a field that is set to "yes" in the
present table. I work for a charity and the committee want the Individual
members to be held in a seperate table. People in the office need to be able
to look at information about members and do this via a form which displays
all the details of a member. However as they do not know if the enquiry is
about a family member or an individual member, we need to display all members
using the same form. Hope that make my design problem easier to understand.
Thanks.

"Gina Whipp" wrote:

Cynw,

Let's start from the beginning why did *whoever* think families should be
segregated from individual member? My better way is put them back into one
table and use a field to indicate if it is an individual. Any if *whoever*
wants to see all the individuals you can run a query based on the before
mentioned field.

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

"Cynw" wrote in message
...
At present I have a membership database with all members held in one
table. I
have been asked to split this table into two tables - family and
individual
members. The user still wants to view all the records in both tables using
the same form as at present. However if I use a UNION query on the two
tables
and link the form to this query, I have found that although I can view all
the records from both tables, I now cannot update them.
Any advice on a better way to design and link the tables would be
appreciated.



.

  #4  
Old January 8th, 2010, 05:57 PM posted to microsoft.public.access.tablesdbdesign
Gina Whipp
external usenet poster
 
Posts: 3,500
Default Updating two identical formatted tables with one form

Cynw,

You say they are in the same table then no Union query needed. You can run
query and sort by the Yes/No field and all the Individuals will be lumped
together and your query will be updateable. Explain to them that putting
indivduals in a seperate table will cause you to have to use a Union query
which can't be updated AND make it harder to maintain ALL members.

Also, as I stated before you can run a query just showing individual
members... Why would that not work for them? So in essence three queries
needed...

1. All members who are a in a family
2. All individual members
3. All members which can be sorted to show family/individuals

Now to help the out on you form you can place a field with something like
this to help *see* what the member type is...

ControlSource of the field: =IIf([YesNoField]=-1,"Individual","Family")

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

"Cynw" wrote in message
...
The individual members already have a field that is set to "yes" in the
present table. I work for a charity and the committee want the Individual
members to be held in a seperate table. People in the office need to be
able
to look at information about members and do this via a form which displays
all the details of a member. However as they do not know if the enquiry is
about a family member or an individual member, we need to display all
members
using the same form. Hope that make my design problem easier to
understand.
Thanks.

"Gina Whipp" wrote:

Cynw,

Let's start from the beginning why did *whoever* think families should be
segregated from individual member? My better way is put them back into
one
table and use a field to indicate if it is an individual. Any if
*whoever*
wants to see all the individuals you can run a query based on the before
mentioned field.

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" -
Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

"Cynw" wrote in message
...
At present I have a membership database with all members held in one
table. I
have been asked to split this table into two tables - family and
individual
members. The user still wants to view all the records in both tables
using
the same form as at present. However if I use a UNION query on the two
tables
and link the form to this query, I have found that although I can view
all
the records from both tables, I now cannot update them.
Any advice on a better way to design and link the tables would be
appreciated.



.



 




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


All times are GMT +1. The time now is 12:15 PM.


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