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

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
  #2  
Old November 24th, 2008, 08:08 PM posted to microsoft.public.access.tablesdbdesign
Steve[_57_]
external usenet poster
 
Posts: 598
Default Multitable searching

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.

BTW, if you need to outsource any Access, Excel or Word work, I provide help
for a very modest fee. Just contact me at .

Steve



"CorporateQAinTX" wrote in
message ...
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



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

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

"Steve" wrote:

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.

BTW, if you need to outsource any Access, Excel or Word work, I provide help
for a very modest fee. Just contact me at .

Steve



"CorporateQAinTX" wrote in
message ...
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




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

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

"Steve" wrote:

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.

BTW, if you need to outsource any Access, Excel or Word work, I provide help
for a very modest fee. Just contact me at .

Steve



"CorporateQAinTX" wrote in
message ...
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




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

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!
  #7  
Old November 24th, 2008, 09:32 PM posted to microsoft.public.access.tablesdbdesign
John... Visio MVP
external usenet poster
 
Posts: 900
Default Multitable searching

"CorporateQAinTX" wrote in
message ...
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

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

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



  #9  
Old November 24th, 2008, 09:41 PM posted to microsoft.public.access.tablesdbdesign
Fred
external usenet poster
 
Posts: 1,451
Default I put this in the wrong place.


My last comment should have been under the original posters last comment,
not under John's.
  #10  
Old November 24th, 2008, 09:49 PM posted to microsoft.public.access.tablesdbdesign
John... Visio MVP
external usenet poster
 
Posts: 900
Default I put this in the wrong place.

"Fred" wrote in message
...

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



;-)

 




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 01:49 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.