Multitable searching

[CorporateQAinTX]

I'm posting this in the design group because I'm actually trying to think of
different ways to design this thing. I say thing because it's become more of
a monster then a dbase. And one quick note, please don't comment on my naming
conventions. I'm not a professional nor will I ever expect to be one. But I
am far more capable then anyone else in my company and figure I will continue
to be the closest thing to an MVP that they'll have without outsourcing. No
offense to anyone. : )

On to the problem. 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 and just set the input form to change
it's source table based on which location they choose on the Main Menu. My
issue is that I can't use my queries or reports anymore. Is it possible to
query the same field over multiple tables. For example, every table has the
TicketNum field. Can I search for a specific TicketNum across 7 tables? Or is
this just a bad design all together? I can code the form to change the source
table based on which location button is chosen, but I can't do the same for
the queries. It has to search all tables for the same thing.

I'm pretty sure I'm not making the best sense, but any suggestions would be
helpful. And if I need to clarify something, please let me know.

Garrett

 

[John... Visio MVP]

BTW, if you need to outsource any Access, Excel or Word work, I provide
help for a very modest fee. Just contact me at (e-mail address removed).

Steve

Okay class, repeat after me, "These newsgroups are provided by Microsoft for
FREE peer to peer support. Stevie is a known troll who really does not grasp
that simple fact"

Stevie, your proposal is totally immodest and it is obvious that you lack
the skills for competing with the professionals. We are still waiting for
you to update your PCDatasheet website with a hint of content. What is it?
About six years of "Coming Soon!!!"

John... Visio MVP

 

[CorporateQAinTX]

Steve,
Thanks for the help. I hadn't thought of that. It would definately cut down
the tables and prevent mistakes. As for the outsourcing, I'll keep you in
mind, but I hate to tell you, one of our key principals is to be the "lowest
cost provider" which usually gets me a big fat NO when I request to spend
money. But like I said, I'll keep you in mind.

Thanks again,
Garrett

 

[CorporateQAinTX]

I thought of one other thing. I've already got 2 forms for entering data. One
is used to enter new data and its source is the main table. The second is for
editing data and its source is a query. The query prompts the user for a
ticket number and then produces the previously completed form for editing, or
at least that's the way it looks to the user. Is it possible to use the same
form for both. I've set the form's Data Entry Only property to yes. So they
can't just scroll through all of the records. Is there a search code that I
can put in the background to find a record when the user enters a ticket
number? If not, then 7 forms turn into 14.

Thanks again

 

[CorporateQAinTX]

Steve,
Nevermind...I figured it out on my own.

And John, I understand that this is a place for FREE help and I'm pretty
sure everyone else does as well. I'm the one that made the note regarding
outsourcing. Please keep the public downgrading off of my posts. I may not be
a professional db programmer, but at least I act with some professional
ettiquette. (Which is far more important in the business world!)

Thanks again Steve!

 

[John... Visio MVP]

Steve,
Nevermind...I figured it out on my own.

And John, I understand that this is a place for FREE help and I'm pretty
sure everyone else does as well. I'm the one that made the note regarding
outsourcing. Please keep the public downgrading off of my posts. I may not
be
a professional db programmer, but at least I act with some professional
ettiquette. (Which is far more important in the business world!)

Thanks again Steve!

The problem is that if we let steve's behaviour be tolerated, it gives
credibility to anyone else who wants to spam the newsgroups with ads and
self promotion. I have seen a number of non Microsoft newsgroups go down the
tubes because the amount of noise overwhelmed the real questions and an
answers. At that point, people started to stay away from the noise and
avoided posting questions in fear that they would be assaulted by offers.
The newsgroups then died.

Steve is not a newbie at this and has been warned many times over the years
about his behaviour, but he persists and occassionally it is necessary to
get out the wet newspaper and tap him on the nose. Unlike a puppy, there is
no chance that steve will be house broken, so the only purpose of the posts
are to remind others that steve's type of behaviour is not tolerated.

As to professionalism, I would remind you that if you choose to deal with
steve, caveat emptor.

John... Visio MVP

 

[Fred]

If you are after etiquette and have been watching these forums, I think that
you would understand that you directed your comments at the wrong person.

Fred

 

[Fred]

My last comment should have been under the original posters last comment,
not under John's.

 

[John... Visio MVP]

My last comment should have been under the original posters last comment,
not under John's.

;-)

 

[CorporateQAinTX]

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.

 

[Clifford Bass]

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

 

[Fred]

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

 

[John... Visio MVP]

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

 

[CorporateQAinTX]

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.

 

[CorporateQAinTX]

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]

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]

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.

 

[Clifford Bass]

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

 

[David W. Fenton]

=?Utf-8?B?Q29ycG9yYXRlUUFpblRY?=

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]

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.