Querying Criteria based on Partial field value (1 Viewer)

[AJordan]

To be more specific Im working on a project where the users have used a MS Excel workbook to keep track of projects (Due Dates, Status, Project name and # etc). They now have thousands of records and find themselves overwhelmed. I've managed to import all the data into Access tables and create easy to use forms. However I'm running into an issue with criteria based on Project number.

The have create a sequence where the first 3 digits represents the office, (ie 282) followed by a dash and then the project order (ie 01, 02, 03 etc). SO the scheme would read Office 282 project number 1. They then added a task identifier using the ".1" scheme. So you would end up with something like 282-01.1.

I only need information based on the "282-01" level and not down to the task level. How can I make a query only retrieve the highest level (282-01) and not include the sub tasks?

 

[plog]

The short answer is to take a look at the MID function (try this post: http://www.access-programmers.co.uk/forums/showthread.php?t=217701).

The long answer involves me yelling at you to properly normalize your table structure. Let me know if you want to go down that road, because in the long wrong its the proper way to do it and will prevent other headaches.

 

[AJordan]

I Looked into that awhile earlier but the point I'm at now, doesnt really help. I wish I could have properly created this table but that is not an option, and migrating to a correct table format is a long way away (change is a scary thing for these people). Unless Im missing something the MID function cannot NOT include a row.

SO if the table is:

282-1 - In Progress
282-1.1 - Complete
282-1.2 - Complete
282-1.3 - Complete
282-2 - Complete
282-2.1 - Complete

I only want to return:
282-2, since it is a project (not a complete task)

 

[Brianwarnock]

Where left(yourfield,Instr(yourfield,".")-1) =

You could group on this also

Brian

 

[plog]

>> change is a scary thing for these people <<

Then why move to Access? Its like living in 1910, recognizing you need to buy a car but instead of running the engine you hitch up a team of horses to it because that's the way your used to.

As to your problem, are you saying '282-1 - In Progress' is all one field's worth of data?

 

[AJordan]

“If I had asked people what they wanted, they would have said faster horses.”

In a nutshell I am creating the database so that my analyst can respont back to the customer in a more timely and accurate fashion. As time goes on we will slowly migrate the cutomer into using the Access database.

The fields are:
Project #
Project Name
Due Date
Status
Comments
Contact

The customer wants to be able to have a list of projects all completed in 2010 (for example). The problem is that they also want to include the "main project" even if it is not "Complete". All the appropriate data is in the correct fields however my current query only returns items that are complete.
Example
282-1 - In Progress
282-1.1 - Complete
282-1.2 - Complete

I need it to return all the items above and not just the ones listed complete, basically keeping the sub tasks attached to the main project

 

[Brianwarnock]

TThat is different , to me anyway, from what you said in Post #3, that is obtained with the Like
Like "282*"

However I guess your example is simplistic

You need to run a query selecting all records with complete but grouping on just the first 3 digits of the id then join this back to the main table again selecting only the 3 digits and pulling all of the records.

Actually I'm not sure that you can use a partial field in a join in which case no join just a criteria left(maintableid,3) = queryid

Brian