Help with some Queries

[stuartk]

Hi All

As I said in my introduction, I am new to Access and need help to manage our people data better.

OK we have Access 2003 (due for upgrade asap) and we have people (guests) fill out paper forms which include things like:

Name
Address
Email
Phone number
Mobile number
Date attended

Now what I want to be able to do is run queries on:

All males / females outputting their names and addresses (for mail merge / letters)
All males / females with NO physical address but have an email address
All persons who attended after a certain date

Hope that is enough info and any help is greatly appreciated

Regards

 

[JohnLee]

Hi,

Does your form include information pertaining to their gender?

If it does then all you need to do is;

a. Filter by Gender. for instance in the criteria row beneath the Gender field enter in the gender type i.e. Male or Female or M or F according to how you define the gender types.

b. Filter for Gender where the address is not null. For instance in the criteria row beneath the Address field enter something like this Is Not Null

c. Filter for Gender where is email address is not null and address is null. For instance in the criteria row beneath the email address field enter something like this Is Not Null

d. Create a date criteria in your query that prompts the user to enter a specific date. For instance in the criteria row beneath the Date field enter something like this [Enter a Date] or if you wanted to have a date range you would enter something like this Between [Enter Start Date] and [Enter End Date]. The user when the query is actioned will be prompted to enter a date according to which method you use.

The above are seperate queries which would be based on the table(s) which hold the relevant information.

I hope this is of assistance

John

 

[stuartk]

Hi,

Does your form include information pertaining to their gender?

If it does then all you need to do is;

a. Filter by Gender. for instance in the criteria row beneath the Gender field enter in the gender type i.e. Male or Female or M or F according to how you define the gender types.

b. Filter for Gender where the address is not null. For instance in the criteria row beneath the Address field enter something like this Is Not Null

c. Filter for Gender where is email address is not null and address is null. For instance in the criteria row beneath the email address field enter something like this Is Not Null

d. Create a date criteria in your query that prompts the user to enter a specific date. For instance in the criteria row beneath the Date field enter something like this [Enter a Date] or if you wanted to have a date range you would enter something like this Between [Enter Start Date] and [Enter End Date]. The user when the query is actioned will be prompted to enter a date according to which method you use.

The above are seperate queries which would be based on the table(s) which hold the relevant information.

I hope this is of assistance

John

Hi John

Thanks for the above advice although some of it is way over my head / experience.

Anyway with regards to running a query of Male or Females, I created 2 separate queries where Male then Female are entered into the criteria box.

Is that correct?

Regards

 

[JohnLee]

Hi Stuartk,

I'm assuming that you have define your gender types as either M or F, so I would create my queries as follows:

1st query - Address field is not Null

Field Row: strGenderType 'Your gender field name should go here
Criteria Row: [Enter Gender] 'The user will be prompted to enter the gender type when the query is actioned.
Field Row: strAddress 'Your address field name should go here
Criteria Row: Is Not Null

This will enable give one query dealing with either M or F for address fields that are not empty.

Using the above as an example you can then create your other queries accordingly.

I Hope this helps

John

 

[stuartk]

Hi Stuartk,

I'm assuming that you have define your gender types as either M or F, so I would create my queries as follows:

1st query - Address field is not Null

Field Row: strGenderType 'Your gender field name should go here
Criteria Row: [Enter Gender] 'The user will be prompted to enter the gender type when the query is actioned.
Field Row: strAddress 'Your address field name should go here
Criteria Row: Is Not Null

This will enable give one query dealing with either M or F for address fields that are not empty.

Using the above as an example you can then create your other queries accordingly.

I Hope this helps

John

Hi John

We enter either Male or Female via a drop down box.

Thanks again for your reply

 

[JohnLee]

Hi Stuartk,

That's fine, but a drop down box doesn't appear in a query, so the criteria in the query would be the same. When you get prompted for the gender type you would enter either Male of Female and the query will work just the same.

John

 

[stuartk]

Hi Stuartk,

That's fine, but a drop down box doesn't appear in a query, so the criteria in the query would be the same. When you get prompted for the gender type you would enter either Male of Female and the query will work just the same.

John

Hi John

Yes that works fine

OK Ive set these queries up for my use of the database, but can I push these queries to other users of the database as they wont have these query settings?

Regards

 

[JohnLee]

Hi Stuartk,

Yes you can. Are your databases linked or are they replica databases of yours?

John

 

[stuartk]

Hi Stuartk,

Yes you can. Are your databases linked or are they replica databases of yours?

John

Hi John

Not sure what you mean by linked or replica!

OK the way I set this up is by creating a database called Guest Info, then split the database into a frontend and backend, with the backend residing on shared folder on our server.

Other end users have a copy of my frontend but if I dont copy any changed versions to their profile then they will have an outdated / unchanged frontend.

So ideally I'd like any changes I make to my frontend to replicate to other end users.

Hope that helps!

 

[JohnLee]

Hi Stuartk,

Yes that's what I meant, FrontEnd/BackEnd. So yes copy the updated queries to your frontend.

John

 

[stuartk]

Hi Stuartk,

Yes that's what I meant, FrontEnd/BackEnd. So yes copy the updated queries to your frontend.

John

Hi John

Thanks for your reply.

Anyway I have been updating the queries on my frontend. However I have another user that I would like to push these queries to.

Should I have been updating any queries at the backend and then copying to frontend?

Regards