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
  #11  
Old November 24th, 2008, 10:03 PM posted to microsoft.public.access.tablesdbdesign
CorporateQAinTX
external usenet poster
 
Posts: 32
Default Multitable searching

Ok, ok...I get it now. Steve, the help you offered does seem to be useful
compared to what I had been considering. But, John and Fred are right. All I
had to do was a quick Google of your email address you gave me and I found
all of the times you've tried to sell your services on these newsgroups.
Solicitation without request IS NOT good ettiquette. John, I profusely
apologize for my ignorance. And Fred, thank you for backing John as well.

I also realized just how new you are at this and it begs the question of
whether you really understand this stuff like you say you do, Steve.

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? This is something that could inevitably affect my company
across the country and I don't think I want to trust it to someone who I
can't trust to be truthful with me. Could we set Steve aside for a sec and
deal with helping me with this issue first?

Lastly, Steve, if you can't visit these sites without soliciting
unsuspecting readers like some vulture then how can we trust you enough to
pay you that "modest fee" and get a service that's worth it?

Thanks folks, and I'm sorry if I may have offended anyone.
  #12  
Old November 24th, 2008, 10:39 PM posted to microsoft.public.access.tablesdbdesign
Clifford Bass[_2_]
external usenet poster
 
Posts: 1,295
Default Multitable searching

Hi Garrett,

Steve's suggestion was a good one. However, my preference would be not
to put the default in the form, but to store it in a settings table. Then
when the form is opened you can query the settings table and set the field's
appropriate default. Or, you can just store it in a variable that is private
to the form and use the form's before update event to set the value. And,
with a settings table, you can link it into your various queries if needed.

Clifford Bass

"CorporateQAinTX" wrote:

Ok, ok...I get it now. Steve, the help you offered does seem to be useful
compared to what I had been considering. But, John and Fred are right. All I
had to do was a quick Google of your email address you gave me and I found
all of the times you've tried to sell your services on these newsgroups.
Solicitation without request IS NOT good ettiquette. John, I profusely
apologize for my ignorance. And Fred, thank you for backing John as well.

I also realized just how new you are at this and it begs the question of
whether you really understand this stuff like you say you do, Steve.

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? This is something that could inevitably affect my company
across the country and I don't think I want to trust it to someone who I
can't trust to be truthful with me. Could we set Steve aside for a sec and
deal with helping me with this issue first?

Lastly, Steve, if you can't visit these sites without soliciting
unsuspecting readers like some vulture then how can we trust you enough to
pay you that "modest fee" and get a service that's worth it?

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

  #13  
Old November 24th, 2008, 10:44 PM posted to microsoft.public.access.tablesdbdesign
Fred
external usenet poster
 
Posts: 1,451
Default Multitable searching

I'm strong on the practical / organizational implementation and structure of
data side, and weak on the developer side of Access. With that
disclaimer.....

Before the others responded, I was going to say that if you are letting your
users put data into your database, you are letting them off the hook too
easily in saying that they can't be expected to pick their location from a
list of 7 with a mouse.

I think that Steve's way would wok and is pretty straightforward. However,
that does leave you with 7 different front ends, each time you make a
different FE change that would be a lot of work.

Maybe you could just force them to pick their name from a dropdown list
(presumably they can get THAT right! :-) and then that would load their
location number from a 2 column lookup table.

Sincerely,

Fred

  #14  
Old November 24th, 2008, 10:45 PM posted to microsoft.public.access.tablesdbdesign
John... Visio MVP
external usenet poster
 
Posts: 900
Default Multitable searching

"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

  #15  
Old November 24th, 2008, 10:54 PM posted to microsoft.public.access.tablesdbdesign
CorporateQAinTX
external usenet poster
 
Posts: 32
Default Multitable searching

That's pretty much what I've already got. It's not an issue of trust on their
parts, it's that with past experience in this company, we have a lot of
people that just click and don't pay attention to what they click every time.
And in this case, I can't be responsible for checking to make sure that
everyone is listing their information correctly every day. So, I'm doing the
thinking for them unfortunately. Too bad we don't get paid extra for that
huh? Thanks for the help though.

"Fred" wrote:

I'm strong on the practical / organizational implementation and structure of
data side, and weak on the developer side of Access. With that
disclaimer.....

Before the others responded, I was going to say that if you are letting your
users put data into your database, you are letting them off the hook too
easily in saying that they can't be expected to pick their location from a
list of 7 with a mouse.

I think that Steve's way would wok and is pretty straightforward. However,
that does leave you with 7 different front ends, each time you make a
different FE change that would be a lot of work.

Maybe you could just force them to pick their name from a dropdown list
(presumably they can get THAT right! :-) and then that would load their
location number from a 2 column lookup table.

Sincerely,

Fred

  #16  
Old November 24th, 2008, 11:00 PM posted to microsoft.public.access.tablesdbdesign
CorporateQAinTX
external usenet poster
 
Posts: 32
Default Multitable searching

Thank you Clifford,

But how would this be different then setting the default for the field? I
still have to have 7 forms. Plus that would add a new table. Do you have an
example of this? Or can you explain the settings table? I like the variable
part though. I could get rid of the List Box I'm using and just use a coded
in variable, right? That would free up some memory at least.

"Clifford Bass" wrote:

Hi Garrett,

Steve's suggestion was a good one. However, my preference would be not
to put the default in the form, but to store it in a settings table. Then
when the form is opened you can query the settings table and set the field's
appropriate default. Or, you can just store it in a variable that is private
to the form and use the form's before update event to set the value. And,
with a settings table, you can link it into your various queries if needed.

Clifford Bass

"CorporateQAinTX" wrote:

Ok, ok...I get it now. Steve, the help you offered does seem to be useful
compared to what I had been considering. But, John and Fred are right. All I
had to do was a quick Google of your email address you gave me and I found
all of the times you've tried to sell your services on these newsgroups.
Solicitation without request IS NOT good ettiquette. John, I profusely
apologize for my ignorance. And Fred, thank you for backing John as well.

I also realized just how new you are at this and it begs the question of
whether you really understand this stuff like you say you do, Steve.

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? This is something that could inevitably affect my company
across the country and I don't think I want to trust it to someone who I
can't trust to be truthful with me. Could we set Steve aside for a sec and
deal with helping me with this issue first?

Lastly, Steve, if you can't visit these sites without soliciting
unsuspecting readers like some vulture then how can we trust you enough to
pay you that "modest fee" and get a service that's worth it?

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

  #17  
Old November 24th, 2008, 11:21 PM posted to microsoft.public.access.tablesdbdesign
Clifford Bass[_2_]
external usenet poster
 
Posts: 1,295
Default Multitable searching

Hi Garrett,

It is true that it is setting something somewhere, whether in a form or
in a table. Defintely though, you will need only one form. A portion of the
form's code might look like this:

Private m_intLocationID As Integer

Private Sub Form_Open(Cancel As Integer)

m_intLocationID = DLookup("LocationID", "tblSettings")

End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)

[txtLocationID] = m_intLocationID

End Sub

Hope that helps,

Clifford Bass

"CorporateQAinTX" wrote:

Thank you Clifford,

But how would this be different then setting the default for the field? I
still have to have 7 forms. Plus that would add a new table. Do you have an
example of this? Or can you explain the settings table? I like the variable
part though. I could get rid of the List Box I'm using and just use a coded
in variable, right? That would free up some memory at least.

  #18  
Old November 24th, 2008, 11:30 PM posted to microsoft.public.access.tablesdbdesign
CorporateQAinTX
external usenet poster
 
Posts: 32
Default Multitable searching

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


  #19  
Old November 24th, 2008, 11:31 PM posted to microsoft.public.access.tablesdbdesign
Clifford Bass[_2_]
external usenet poster
 
Posts: 1,295
Default Multitable searching

Hi Garrett,

Further information. A settings table, at least as I use it, contains
only one row. The contents of that row are used for holding global settings
for the program, as run at a particular site, such as your LocationID, and/or
as run with particular options. I often use a separate integer field as the
primary key. I make its default 1 and set its validation condition to 1.
That way the row is easily added, and it enforces that there is only one row.
The settings table then can be read in at start up, when opening forms or
reports, in code, or with queries.

Clifford Bass

"CorporateQAinTX" wrote:

Thank you Clifford,

But how would this be different then setting the default for the field? I
still have to have 7 forms. Plus that would add a new table. Do you have an
example of this? Or can you explain the settings table? I like the variable
part though. I could get rid of the List Box I'm using and just use a coded
in variable, right? That would free up some memory at least.

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

=?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/
 




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 11:36 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.