Jump to content


 


Register a free account to unlock additional features at BleepingComputer.com
Welcome to BleepingComputer, a free community where people like yourself come together to discuss and learn how to use their computers. Using the site is easy and fun. As a guest, you can browse and view the various discussions in the forums, but can not create a new topic or reply to an existing one unless you are logged in. Other benefits of registering an account are subscribing to topics and forums, creating a blog, and having no ads shown anywhere on the site.


Click here to Register a free account now! or read our Welcome Guide to learn how to use this site.

Photo

Microsoft Access - Problem running query


  • Please log in to reply
4 replies to this topic

#1 doconnsull

doconnsull

  • Members
  • 2 posts
  • OFFLINE
  •  
  • Local time:02:46 PM

Posted 21 June 2005 - 06:00 PM

Hello, I'm glad to have found this forum and hope someone can help me.

I am a new Access user within the past 6 months. My company already had all of their databases set up when I came so it was easy enough to just add or edit information.

Now I have been asked to set up a query based on certain fields in one of the tables. I'll try to be brief with this:

We have a table containing all of our client information, name, address, phone numbers, date of their appointments, who referred them, clothing sizes, etc. For the new query we only need their name, who referred them, the date of their appointment and the phone numbers. We want this new query only to show people in the past 6 months for our follow up person to call.

When I tried to create and run the query, it gave me just the fields I wanted, but of course gave me all dates, going back to 2001. Again, I only want to see from January 2005 to present. So I sorted it by date and deleted everything prior to Jan. 2005. We do want the new information from the client table to keep updating in the new query, we just don't want the old stuff. I had the list whittled down, all looked good, and then bam - I saved the new query and closed it, and when I went back to the client table, everything prior to Jan. 2005 was deleted from there too.

Luckily we had a back up and were able to restore it (except that now the primary key numbers are wayyyy off but we really don't use that anyway).

So is there a way to create this query that I'm talking about, without losing all the data on the original table? I noticed that after we restored the original table, all of the old information came back to the new query as well.

Thanks in advance for any help.

BC AdBot (Login to Remove)

 


m

#2 tos226

tos226

    BleepIN--BleepOUT


  • Members
  • 1,568 posts
  • OFFLINE
  •  
  • Gender:Female
  • Location:LocalHost
  • Local time:02:46 PM

Posted 21 June 2005 - 08:47 PM

The answer to your final question is YES.
We have a bit of a problem with terminology here.
I'm not sure whether you do or do not want to delete all data prior to 6 months back. Let's assume you don't. Under normal (non-maintenance) circumstances, queries are used just to retrieve records you want to see.

In the query pane, under the fields is a Criteria row.
Look in HELP for Criteria.
I'll try to give you some pseudo-syntax.
What you need is to write something to the effect fieldName>=Today()-180 (roughly 180 days), the syntax might be Now()-180 (or is that in Excel?). Or you can fix the date and say >= '1/1/2005' (again, look in help for exact syntax), though I would not recommend ever using a constant or every couple months you'll have to edit the query to be current.

An alternative is to ask the user for a date range to display. I think that's called parameter queries in Access. Again look it up in help.

I haven't used Access in couple years, but I believe if in the query designer you double click in the cell under the field name, Access takes you to the expression writer where your fields and allowed function are displayed. Between help and the functions which you paste quite easily, nesting included, I think you'll get what you want this query to return, with your data untouched, as I feel it should be unless your company dictates otherwise.

In the same grid of fields and rows, you can specify the sequence of fields by dragging them around and you can specify the sort.

Have fun :thumbsup:

Edited by tos226, 21 June 2005 - 08:49 PM.


#3 tos226

tos226

    BleepIN--BleepOUT


  • Members
  • 1,568 posts
  • OFFLINE
  •  
  • Gender:Female
  • Location:LocalHost
  • Local time:02:46 PM

Posted 21 June 2005 - 08:53 PM

Luckily we had a back up and were able to restore it (except that now the primary key numbers are wayyyy off but we really don't use that anyway). 

You may want to rethink this one. Some primary key is used now or will be in the furure as a unique ID of the customer. A cleanup of the primary key might be in order. But that's a whole other story.

Day later - I just checked few things to fill some holes:
1. In the query Criteria row, it's not the double-click but right click which takes you to expression builder (select Build initially, zoom to look it over).
2. The date filter syntax would be ">Now()-180" without the quotes.
3. AND criteria are in the same row across one or more fields.
4. Alternatives (OR) are written down for each field where OR applies.

Edited by tos226, 22 June 2005 - 03:05 PM.


#4 doconnsull

doconnsull
  • Topic Starter

  • Members
  • 2 posts
  • OFFLINE
  •  
  • Local time:02:46 PM

Posted 23 June 2005 - 10:25 AM

In the query pane, under the fields is a Criteria row.
Look in HELP for Criteria.
I'll try to give you some pseudo-syntax.
What you need is to write something to the effect fieldName>=Today()-180 (roughly 180 days), the syntax might be Now()-180 (or is that in Excel?). Or you can fix the date and say >= '1/1/2005' (again, look in help for exact syntax), though I would not recommend ever using a constant or every couple months you'll have to edit the query to be current.

Thank you so much for your response.

I am in the criteria box, and I understand what you're saying, but I am a totally dummy here - I still can't figure out EXACTLY what to write in that box. I just don't understand if I should be leaving the brackets and typing within, or just typing the "formula."

Thanks.

#5 tos226

tos226

    BleepIN--BleepOUT


  • Members
  • 1,568 posts
  • OFFLINE
  •  
  • Gender:Female
  • Location:LocalHost
  • Local time:02:46 PM

Posted 23 June 2005 - 12:08 PM

1. In the query Criteria row, it's not the double-click but right click which takes you to expression builder (select Build initially, zoom to look it over).
.

I am assuming you reached this point, I have to assume something or can't answer :thumbsup:
If you right clicked in the criteria cell and it took you to the Expression builder box, you can just type your expression here, such as >Now()-180. You do not need to write the date's field name here, you're already on it, so the issue of brackets should not arise (I assume you're talking about [those] ).
You can use the provided buttons for operators and click on them to enter > or = or +... there is a bunch of them, though typing seems simple in this instance.

When, (and I don't think you need it here), you want to enter some other field name than the one where it all started, select it from the panes , under Tables. Access witll put square brackets around the table name and field name and it will look like this: [customersTable]![clientID field] . Leave that alone, just continue with the expression.

No more guessing on my part; this time I'm actually looking at Access.

Edited:
To clarify, since this is difficult to convey without screen shots:
In the Query designer window, at the intersection of the Criteria row and dCall column (dCall is my field name), I see >=Now()-3. When I get to the Expression builder, I see identical expression. No brackets, no quotes, nothing.

One thing you may wish to do is to look at the famous Northwind database which Access provides as an example during installation. There you can see how this sort of things are done. In a standard installation you should be able to find it in C:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb
I see here two places which might help you:
Current Product List query: uses NO as a criteria for field Discontinued
Quarterly Orders query: OrderDate uses fixed date range - Between #1/1/1997# And #12/31/1997#

Edited by tos226, 23 June 2005 - 12:47 PM.





0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users