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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|