Noob with questions! How original... :)

[Benzsupra]

Hi everyone, been looking around the threads here for a while now trying to find an answer to my question but i've had no luck. I'm not an Access guru or anything but i can build simple databases, not got a clue with VB at all. I've been asked to produce a database at work for customer complaints and i've managed to sort out the basics but i'm having problems with a few things.

I've been asked to have a way to access all of the complaints not actioned within a 2 week period. I have a date field and a actioned yes or no field but i don't know where to go from here.

Has anyone had a similar database or know how i would get this happening? Sorry for the long question as my first post and if this isn't in the right place mod's feel free to move it.

Thanks everyone.

Ben
BTW i'm using Access 2002

 

[pbaldy]

Welcome to the site. Try a query like this:

SELECT...
FROM TableName
WHERE Date() - DateField > 14 AND Actioned = False

which obviously is based on the current date. If you're looking for older items that weren't done within 2 weeks, you'd have to store the date they were action-ed.

 

[David Eagar]

Create a query with criteria where the date is >Date()-14 and Actioned = false

ps make sure your Date field is not named as date as this is a reserved word

Doh, pbaldy beat me to it

 

[Benzsupra]

Sorry to be a pain but as i said i only know the very basics. I've never used the queries before but i've attached a pic of how i interpreted your responses. As you are probably going to guess it didn't work Could you point me in the direction on how to properly set it up? Thanks you all for your replies i was expecting to be shot down for asking questions with my first post!

 

[David Eagar]

Dumb question, but if you have never used queries before, how come you've been asked to create this database? What tools are you using for your training?

ps Don't be put off by this response, one of the key functions of this forum is that 'we will help those that help themselves'.

 

[DreamGenius]

David raises a very valid question. If you're not sure what you're doing and this is a production system, you should say so.

However, on the help front, if you do the following, I'll try to correct/recreate your query:

1. Take yourself back to the point at which you took the above screen shot
2. Using the View menu, change to SQL view
3. Copy the content of the Query window and paste it here.
4. Wait for a response from the forum with corrected code
5. Paste the SQL back in place of what you copied
6. Using the View menu, change back to Design view
7. See how this has affected your query, and so where you were going wrong

It's not easy trouble-shooting from just a screen shot.

 

[David Eagar]

I would go back 1 step further - if you don't know queries, forget sql

From your table, run a query wizard - and if your table has no valid data, with the criterias added, don't be surprised if you get no results

The aim is to learn to THINK about how Access works, not to get someone to solve your problems for you

 

[Benzsupra]

I understand where you're all coming from, this is a learning exercise for me and my company doesn't require this as an integral work system. It was an idea brought up at a meeting and i thought i may be able to extend my knowledge of Access by trying to get something in place. I know i have only skimmed the surface of the program itself but i have no way of learning other than trial and error or resources gained from the internet. I in no way want this to be done for me as i'll never learn anything! I have built some very simple databases using just records, forms and reports that can get stored, printed and e-mailed with a pretty interface. I'm more than willing to help myself but if i'm being brutally honest i don't know what a query is or what it does so maybe i need to start there but i could not find any direct information relating to that either.
I do not have the time to attend classes for this and if i did there are none within cycling distance of me. I tried an access self help dvd thing but it was absolute rubbish hence i'm here trying to find out from the experts, the real people.

I'm not having a pop back at anyone i just don't want you all to think i want you guys to do my work for me. I'm willing to learn if i can find the right tools with which to.

Thanks again people.

 

[Benzsupra]

David raises a very valid question. If you're not sure what you're doing and this is a production system, you should say so.

However, on the help front, if you do the following, I'll try to correct/recreate your query:

1. Take yourself back to the point at which you took the above screen shot
2. Using the View menu, change to SQL view
3. Copy the content of the Query window and paste it here.
4. Wait for a response from the forum with corrected code
5. Paste the SQL back in place of what you copied
6. Using the View menu, change back to Design view
7. See how this has affected your query, and so where you were going wrong

It's not easy trouble-shooting from just a screen shot.

Thanks DreamGenius, didn't even know this view existed. the code that comes up when i do that is this:

SELECT Complaints.[Date of investigation]
FROM Complaints
WHERE ((([Complaints].[Date()-"Date of investigation"])>14) AND (([Complaints].["Action completed?"])=False));

I can see where the other posts come into it now!

 

[Benzsupra]

I've tried putting it in how it makes sense to me but i get an "operator missing" error. I put it in like this:

SELECT Complaints.[Date of investigation]
FROM Complaints
WHERE Date() - Date of investigation > 14 AND Action completed = False

 

[DreamGenius]

Then can I suggest that you invest in a book? If your company is serious about assisting you, they should allow you to purchase it on expenses.

I'd strongly recommend the Microsoft Press Step By Step book, which I've linked to on Amazon for the 2003 version. I have a copy myself and refer to it regularly. You'll find that this quickly builds up very basic knowledge to quite sophisticated levels, with screen shots and examples to work on throughout; a CD-ROM is included.

I find that, because of the immediacy of online fora, books are often overlooked as a method of learning. The money invested is often less than the time wasted in searching online!

 

[DreamGenius]

Okay, I'm not promising that this is going to deliver what you're after but I've corrected your syntax. This should make the Query function, even if it doesn't deliver what you think you're asking it to!

SELECT [Complaints].[Date of investigation]
FROM [Complaints]
WHERE (((Date() - [Complaints].[Date of investigation]) > 14) 
AND (([Complaints].[Action completed?]) = False));

A brief explanation/list of assumptions:

• Everything in square brackets exists in your database
• Date() is a dynamic entry that always passes today's date to the query
• Complaints is a table in your database
• Date of Investigation and Action Completed? are fields in table Complaints
• Date of Investigation is set up as a Date/Time field
• Action Completed? is set up as a Yes/No field

Use that as a basis to see where you need to take this.

 

[Benzsupra]

Thanks mate, at the cost of £10 i will get it myself! I'm going to order that book in a moment and see where it get's me. My problem is i only get to play around with Access at work because i've only got a Mac indoors and i can't run it on there.
I've put you're bit of code in, i'll have to create some fake records now and test it out. Thank you very much for your help and the explanation, that was very helpful.

 

[namliam]

Rule #1
Dont use spaces or special charachters )(*&^%?>< in any of your table or column names.

Rule #2
Adhere to a naming convention... All your objects for ease of reference need to be prefixed by what they are. i.e. tblComplaints for your table, qry for Queries etc.

 

[DreamGenius]

I totally agree with that advice but felt that it might be a step too far right now, which is why I suggested the book. I don't have it here with me so can't say whether it makes that suggestion or not. As the majority of queries will be built using the design interface, these problems should not be encountered - they have been because we've exposed, and edited, the SQL view. I'd argue that few MS Access users use it - here be dragons!

 

[Benzsupra]

Just put the code in and it does exactly what i wanted it to do, thanks everyone. I'm sure i'll be back soon!

 

[Benzsupra]

That code worked brilliantly thanks mate, and thanks to everyone else, i'm sure i'll be on here again soon!