Searching Problem

[Grumpy Gamer]

Hi everyone,

I've been roped into fixing up an Access database used for a small
newspaper subscriptions area where I work. The database itself is an
Access 2000 format, and I'm fixing it up (there are a few problems
with it) in Access 2007. I'm not very good with Access, but I've
managed to get all the problems fixed except for one, and this one I
need some help with.

Basically, the database has two tables: subscribers and subscriptions.
Subscribers contains all the subscribers data: name, address, contact
details. Subscriptions contains the details of their actual
subscription - what newspaper, how long their subscription is for etc.
Now, the primary key for the subscribers table is a field called
SubscriberID which is an auto number and is just a new number given to
each new subscriber. This field is also in subscriptions, but the
primary key in subscriptions is the SubscriptionNo field, which again
is an auto number field.

Now, when the newspaper people add a new subscriber record, they use a
form. The form has a sub-form attached. The main part of the form has
as its record source the subscribers table, and this is where they
enter the persons name, address, contact details etc. The sub-form
(called 'all subscriptions') has as its record source a query called
'all subscriptions', and this is where they enter the subscription
details. Now, here comes the problem: the SubscriptionNo field is on
this sub-form. There is a search button on the sub-form (when clicked,
just opens a normal Access 'Ctrl+F' search dialog), but when you type
in a subscription number to search by, only the current subscriber
record gets searched. When you click the search button on the main
form, you get to search the whole subscribers table, but unfortunately
the subscriber number is not in that table - so there is currently no
way to search for a subscriber number, which is what is needed.

I can only see two possibilities here, and I have been unable to get
either of them to work:
1. Add a new field called SubscriptionNo into subscribers, and somehow
link the SubscriptionNo field from subscriptions to that field in
subscribers
2. Somehow edit the 'all subscriptions' query to include every record
in the database - but this would break the entire point of the
database. The all subscriptions query currently ensures that when you
view a subscriber record, only their subscriptions are shown.

Any suggestions are welcome. Remember, I'm not an Access expert.

 

[Grumpy Gamer]

Hi everyone,

I've been roped into fixing up an Access database used for a small
newspaper subscriptions area where I work. The database itself is an
Access 2000 format, and I'm fixing it up (there are a few problems
with it) in Access 2007. I'm not very good with Access, but I've
managed to get all the problems fixed except for one, and this one I
need some help with.

Basically, the database has two tables: subscribers and subscriptions.
Subscribers contains all the subscribers data: name, address, contact
details. Subscriptions contains the details of their actual
subscription - what newspaper, how long their subscription is for etc.
Now, the primary key for the subscribers table is a field called
SubscriberID which is an auto number and is just a new number given to
each new subscriber. This field is also in subscriptions, but the
primary key in subscriptions is the SubscriptionNo field, which again
is an auto number field.

Now, when the newspaper people add a new subscriber record, they use a
form. The form has a sub-form attached. The main part of the form has
as its record source the subscribers table, and this is where they
enter the persons name, address, contact details etc. The sub-form
(called 'all subscriptions') has as its record source a query called
'all subscriptions', and this is where they enter the subscription
details. Now, here comes the problem: the SubscriptionNo field is on
this sub-form. There is a search button on the sub-form (when clicked,
just opens a normal Access 'Ctrl+F' search dialog), but when you type
in a subscription number to search by, only the current subscriber
record gets searched. When you click the search button on the main
form, you get to search the whole subscribers table, but unfortunately
the subscriber number is not in that table - so there is currently no
way to search for a subscriber number, which is what is needed.

I can only see two possibilities here, and I have been unable to get
either of them to work:
1. Add a new field called SubscriptionNo into subscribers, and somehow
link the SubscriptionNo field from subscriptions to that field in
subscribers
2. Somehow edit the 'all subscriptions' query to include every record
in the database - but this would break the entire point of the
database. The all subscriptions query currently ensures that when you
view a subscriber record, only their subscriptions are shown.

Any suggestions are welcome. Remember, I'm not an Access expert.

Oops - should have mentioned - the database users want the search
results to come up in the same form that they enter the details in to
begin with, so the third way I came up with:
create a query to return every record in the database, add a button
for the query onto the sub-form and then show them how to use Ctrl+F
is out too.