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  

Multitable searching



 
 
Thread Tools Display Modes
  #21  
Old November 25th, 2008, 02:02 AM posted to microsoft.public.access.tablesdbdesign
David W. Fenton
external usenet poster
 
Posts: 3,373
Default Multitable searching

"Steve" wrote in
news
Seven tables is a bad idea! Consider using seven identical forms
except for LocationID. Make the LocationID field on the forms
hidden and on each form set the default value for LocationID
appropriate for the location where the form is being used. With
this setup, anytime someone enters data on their form, Access will
automatically assign the appropriate LocationID for the location
where the data is being entered.


This advice is correct. Something I often do when I'm using a single
form to display subsets of data from a single table that needs to be
distinguished by the user is to use different colors on the form for
the different subsets. It might be hard to find 7 memorable colors,
but I've found that users intuitively grasp the color distinction --
they don't even think about it, they just associate it with the
particular data they are seeing, and that in turn helps them to
avoid editing the wrong data.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
  #22  
Old November 25th, 2008, 02:03 AM posted to microsoft.public.access.tablesdbdesign
John... Visio MVP
external usenet poster
 
Posts: 900
Default Multitable searching

I guess we should take a step back.
Does each user work on information about one and only one location?
If so, that is the easiest scenerio. You can create a user profile table and
place the location id in there and use the user's user id as the key.

You may already have a user profile table to store other information about
the user. Full name, department name, printer preference, security or
authorization level or ?

Once they have signed into the PC, there is no need to seperately sign into
Access. Access can determine the login userid.

John.. Visio MVP


"CorporateQAinTX" wrote in
message ...
Now this is what I was looking for...options and less forms.

The users have to sign in to the computer, but not into Access. Now, I
have
been tinkering with user-level securities so I can require each location
to
sign in. I just don't understand how to "tie" the location id to the user
log-in. Technically the user can open the db from any computer linked to
our
network. Our corporate server is accessible from each of our facilities.
They
can read from certain folders and write to even fewer. The main db will be
hidden in one of the folders they can read from and each facility will be
given a shortcut with the /runtime and /wrkgrp lines embedded in it. The
problem in the past is that we have too many people who like to tinker
instead of calling the DB Administrator. Anyway, the login's aren't
session
based. Do you think you can help me John?

Thanks for clearing my head and helping me out of a big mess.

"John... Visio MVP" wrote:

"CorporateQAinTX" wrote in
message ...
John, I profusely apologize for my ignorance.


No apologizing is necessary, you just fell into a trap.

John, Fred, while you've been helping me to see the error of my ways,
did
either of you happen to consider whether Steve's assessment of my
situation
was correct or not?


The single table with a location id is the way to go, but contrary to
what
steve suggested, use a single form with the location id hidden.

Now the trick is determining how you want to set the location id. Of
course
there are a few other questions to ask before you decide on a solution.
1. If the users are required to sign in, you can tie the location id to
their profile.
2. Can a user enter information from more than one location? Possibly
linking the Location id to a menu selection is the way to go.
3. If they can enter information from more than one location, is it
session
baed. Then the point at which you switch sessions can set the Location
Id.

Thanks folks, and I'm sorry if I may have offended anyone.


No offence taken.

John... Visio MVP




  #23  
Old November 25th, 2008, 02:21 AM posted to microsoft.public.access.tablesdbdesign
David W. Fenton
external usenet poster
 
Posts: 3,373
Default Multitable searching

"David W. Fenton" wrote in
36.98:

"Steve" wrote in
news
Seven tables is a bad idea! Consider using seven identical forms
except for LocationID. Make the LocationID field on the forms
hidden and on each form set the default value for LocationID
appropriate for the location where the form is being used. With
this setup, anytime someone enters data on their form, Access
will automatically assign the appropriate LocationID for the
location where the data is being entered.


This advice is correct.


Oops. I missed the 7 identical forms.

Having two or more identical objects in an Access application is a
nightmare for long-term maintenance and an indication of a design
flaw. Information on the location should be applied dynamically when
the forms is opened, according to information supplied by the user
before the form is opened. If they don't supply the information, the
form shouldn't load -- that will prevent them from skipping any of
it.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
  #24  
Old November 25th, 2008, 10:49 AM posted to microsoft.public.access.tablesdbdesign
Keith Wilby
external usenet poster
 
Posts: 812
Default Multitable searching

"CorporateQAinTX" wrote in
message ...

I'll keep you in mind.


Please also keep this in mind:

http://home.tiscali.nl/arracom/whoissteve.html

  #26  
Old December 8th, 2008, 05:39 PM posted to microsoft.public.access.tablesdbdesign
CorporateQAinTX
external usenet poster
 
Posts: 32
Default Multitable searching

Man, I just went through the link you posted. Jeez, does this guy have an ego
or what?!? I'm glad I learned quickly, but isn't there some way for the group
administrators to get together and block this guy somehow?

Anyway, I saw the post and had to check it out.

"Keith Wilby" wrote:

"CorporateQAinTX" wrote in
message ...

I'll keep you in mind.


Please also keep this in mind:

http://home.tiscali.nl/arracom/whoissteve.html


  #27  
Old December 8th, 2008, 06:16 PM posted to microsoft.public.access.tablesdbdesign
CorporateQAinTX
external usenet poster
 
Posts: 32
Default Multitable searching

Sorry for the long delay between posts. I've been out of town for the last 2
weeks. I hope everyone had a good Thanksgiving holiday (if that's your
regional holiday of course).

It is possible for more than one person to access this dbase at the same
time. Unlikely, but possible. Most of them have to log into the PC, but not
all of them have specific logins. Some of them are generic for the computer
to have access to the network. I haven't created a user list table yet. I had
tinkered with the idea, but ended up stumbling across the ULS available in
Access. What I wanted to do was to give each location a general login and
give the Quality Managers an admin log-in. Of course, my position would be
the only one to have complete control. The data entry form is already in use
at a beta site and seems to be working great. I'm just trying to get this
part complete before the end of the year, so I can initiate it at all of our
locations. Is it possible to access the security file to determine who is
logged in and what their corresponding location id would be? Once I can find
a way to set that up, I believe I can get the forms to pull that for the data
entry part. And also be able to determine which location is trying to pull
data for a report.

Thanks for all your help so far. I appreciate the knowledge being shared.

"David W. Fenton" wrote:

=?Utf-8?B?Q29ycG9yYXRlUUFpblRY?=
wrote in
:

I've got 7 facilities that have to keep up with "bad"
product produced. I've got the table design built and implemented
at one facility already. It's working great, but my original plan
was to use one table and have the users select a location which
would store a location ID# in the main table. I could then use
another form attached to a query that would search the main table
based on the location chosen and some other variables available.
Well, come to find out, not everyone can use a mouse correctly. I
can't trust that the user will choose the correct location. So my
plan was to make 7 identical tables


Is there any information known programmatically that Access can look
up that could define the location? That is, if user Sam is logged on
as Sam, wouldn't a table defining his location allow you to
automatically filter for him? If you really want to get fancy and
your Active Directory setup uses Organizational Units, you could get
the data from there.

On the other hand, you may have a situation where the users are
putting in/viewing data on multiple locations, so you can't default
to a particular one. In that case, I'd suggest that you change your
app to use search methods that won't display any data unless the
user has put in a location. That is, the user pulls up a record or a
particular record based on criteria input by the user. If the
criteria aren't completed, they get no data. This would force them
to put in the location in order to be able to view/edit any data at
all.

Exactly how to implement that depends on what tasks you are
performing in your database, so if this seems like the right
direction for your needs, fill us in on more details and I'm sure
we'll have suggestions on how to make it work.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

  #28  
Old December 8th, 2008, 06:24 PM posted to microsoft.public.access.tablesdbdesign
John W. Vinson
external usenet poster
 
Posts: 18,261
Default Multitable searching

On Mon, 8 Dec 2008 08:39:01 -0800, CorporateQAinTX
wrote:

Man, I just went through the link you posted. Jeez, does this guy have an ego
or what?!? I'm glad I learned quickly, but isn't there some way for the group
administrators to get together and block this guy somehow?


If there were group administrators, that would be reasonable; but this is an
open, peer-to-peer support group without moderators.
--

John W. Vinson [MVP]
 




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 10:03 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.