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 » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Previous and Next from Criteria



 
 
Thread Tools Display Modes
  #1  
Old May 3rd, 2010, 10:27 PM posted to microsoft.public.access.queries
Mark S
external usenet poster
 
Posts: 23
Default Previous and Next from Criteria

I am not sure what to even look for here so I wasn't able to do any real
searches to see if this has been answered.

Ok, this isn't exactly what I am doing but is the best way I can think of to
explain it.

Start with a table of address (Name, Street, City, State, etc) and use a
query to sort it to State, City, Street. This should group address on the
same street together. Then add a criteria for only addresses that are
missing the Zip Code.

I would like to be able to see not only the entries that are missing the Zip
Code, but also the one immediately before and after that entry based on the
sort criteria stated above. From this I could make a best guess at what the
Zip Code should be.

Like I said, this isn't exactly what I am trying to do. I am actually
working with an Event Code and Event Description. From previous entries I
know what the description should be for a new code. Doing this at the time
of entry is not possible. Currently I have to look for missing entries in
the entire table.

Any suggestions?
  #2  
Old May 3rd, 2010, 10:39 PM posted to microsoft.public.access.queries
Jeanette Cunningham
external usenet poster
 
Posts: 2,190
Default Previous and Next from Criteria

Make 2 queries.
A query showing the zip code/s for each city will serve as your reference.

In the original query use a criteria of Is Null to get the State and City
for address without zip code.

Now do a DLookup to the city with zip code query to get the zip code for the
city that matches the city of the record with missing zip code.



Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

"Mark S" wrote in message
...
I am not sure what to even look for here so I wasn't able to do any real
searches to see if this has been answered.

Ok, this isn't exactly what I am doing but is the best way I can think of
to
explain it.

Start with a table of address (Name, Street, City, State, etc) and use a
query to sort it to State, City, Street. This should group address on the
same street together. Then add a criteria for only addresses that are
missing the Zip Code.

I would like to be able to see not only the entries that are missing the
Zip
Code, but also the one immediately before and after that entry based on
the
sort criteria stated above. From this I could make a best guess at what
the
Zip Code should be.

Like I said, this isn't exactly what I am trying to do. I am actually
working with an Event Code and Event Description. From previous entries I
know what the description should be for a new code. Doing this at the
time
of entry is not possible. Currently I have to look for missing entries in
the entire table.

Any suggestions?



  #3  
Old May 4th, 2010, 02:44 AM posted to microsoft.public.access.queries
Mark S
external usenet poster
 
Posts: 23
Default Previous and Next from Criteria

The use of Zip Code in the explanation was to try to make it a little more
understandable. I am actually using an Event Code that is extracted monthly
from an Excel table from another party. When a new Event Code is used, I
have to take it and manually add 3 other pieces of information. If I can see
a previous code (ie. AB3) and the other fields, I can usually interpret what
a new code means (ie. ABZ). In this case, the ABx would represent something
significant and I can enter the data from that. But it is not something that
can be done with lookups. I normally have to look at a previous entry or if
there is none I go to another source to decode it.

While I might be able to build some lookups that are partially effective, I
could not trust it to be 100% accurate. The example I gave above is again an
oversimplification. There are essentially several different formats that the
Event Code field could be in, so a lookup based on the first two character
would not work.

Thanks for you input though.


"Jeanette Cunningham" wrote:

Make 2 queries.
A query showing the zip code/s for each city will serve as your reference.

In the original query use a criteria of Is Null to get the State and City
for address without zip code.

Now do a DLookup to the city with zip code query to get the zip code for the
city that matches the city of the record with missing zip code.



Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

"Mark S" wrote in message
...
I am not sure what to even look for here so I wasn't able to do any real
searches to see if this has been answered.

Ok, this isn't exactly what I am doing but is the best way I can think of
to
explain it.

Start with a table of address (Name, Street, City, State, etc) and use a
query to sort it to State, City, Street. This should group address on the
same street together. Then add a criteria for only addresses that are
missing the Zip Code.

I would like to be able to see not only the entries that are missing the
Zip
Code, but also the one immediately before and after that entry based on
the
sort criteria stated above. From this I could make a best guess at what
the
Zip Code should be.

Like I said, this isn't exactly what I am trying to do. I am actually
working with an Event Code and Event Description. From previous entries I
know what the description should be for a new code. Doing this at the
time
of entry is not possible. Currently I have to look for missing entries in
the entire table.

Any suggestions?



.

  #4  
Old May 4th, 2010, 05:23 AM posted to microsoft.public.access.queries
Jeanette Cunningham
external usenet poster
 
Posts: 2,190
Default Previous and Next from Criteria

The difficulty is in knowing which record is a previous code.
How do you manually find the previous code in the table?


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

"Mark S" wrote in message
...
The use of Zip Code in the explanation was to try to make it a little more
understandable. I am actually using an Event Code that is extracted
monthly
from an Excel table from another party. When a new Event Code is used, I
have to take it and manually add 3 other pieces of information. If I can
see
a previous code (ie. AB3) and the other fields, I can usually interpret
what
a new code means (ie. ABZ). In this case, the ABx would represent
something
significant and I can enter the data from that. But it is not something
that
can be done with lookups. I normally have to look at a previous entry or
if
there is none I go to another source to decode it.

While I might be able to build some lookups that are partially effective,
I
could not trust it to be 100% accurate. The example I gave above is again
an
oversimplification. There are essentially several different formats that
the
Event Code field could be in, so a lookup based on the first two character
would not work.

Thanks for you input though.


"Jeanette Cunningham" wrote:

Make 2 queries.
A query showing the zip code/s for each city will serve as your
reference.

In the original query use a criteria of Is Null to get the State and City
for address without zip code.

Now do a DLookup to the city with zip code query to get the zip code for
the
city that matches the city of the record with missing zip code.



Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

"Mark S" wrote in message
...
I am not sure what to even look for here so I wasn't able to do any real
searches to see if this has been answered.

Ok, this isn't exactly what I am doing but is the best way I can think
of
to
explain it.

Start with a table of address (Name, Street, City, State, etc) and use
a
query to sort it to State, City, Street. This should group address on
the
same street together. Then add a criteria for only addresses that are
missing the Zip Code.

I would like to be able to see not only the entries that are missing
the
Zip
Code, but also the one immediately before and after that entry based on
the
sort criteria stated above. From this I could make a best guess at
what
the
Zip Code should be.

Like I said, this isn't exactly what I am trying to do. I am actually
working with an Event Code and Event Description. From previous
entries I
know what the description should be for a new code. Doing this at the
time
of entry is not possible. Currently I have to look for missing entries
in
the entire table.

Any suggestions?



.



 




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 07:46 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.