Query for multiple "not like" criteria on long text fields operating as "or", not "and".

[Missomissou]

I created a query to help me generate a quarterly report for projects with (long text) updates from October to January. It seemed fairly straightforward, but there were apparently catastrophic flaws--the data was imported from Excel spreadsheets and though it looked ok, the formatting was a mess, so I spent some time cleaning it up. Now the query works beautifully. Except... In the criteria for each month's update (all long text fields), I have entered "Not "No new updates"", with the intention that if all four months have "No new update", then that project will be excluded from the query results. But it appears instead as if it's excluding all records that have "No new update" in any one of the months. Any idea what I'm doing wrong here? The query design looks like this:

It is returning 14 records, of about 45 that I was hoping to have included.

Here's a sample of the data:

 

[arnelgp]

can you use Instr(), example:

select ProjectID, ProjectTitle, ...
From YourTableName
Where
Instr(1, October & "", "no new updates") = 0 And
Instr(1, November & "", "no new updates") = 0 And
Instr(1, December & "", "no new updates") = 0 And
Instr(1, January & "", "no new updates") = 0

Also, the table is not Normalized.
table should grow "Tall" not "Wide".

suggestion:

ProjectID (Primary Key)
ProjectTitle
PointOfContact


another table:

JunctionID (autonumber) (Primary Key)
ProjectID (Foreign Key to the first table)
MonthName (string)
Content (the Long text here).

 

[Missomissou]

Hi--if I understand correctly, with your suggested design, this table could capture monthly project updates for all years (MonthName could instead be a date?)? That configuration seems like it would release some of the input control--updates are expected on a monthly basis, but not required. In this case, how would you suggest entering the ProjectID? Would this be an appropriate place for a lookup field in the table, or should I only use that on the form which our staff will use?

 

[MajP]

Hi--if I understand correctly, with your suggested design, this table could capture monthly project updates for all years (MonthName could instead be a date?)?

Yes. If you put in a real date you would know the year and month, but you can format a date in any way and show some or all of it.
1/1/2024 can be displayed as just
January
2024
Monday, 1 January 2024
01/01/24
etc.
So you can store more than you display. Dates are not stored with any format it is a decimal number.

That configuration seems like it would release some of the input control--updates are expected on a monthly basis, but not required. In this case, how would you suggest entering the ProjectID?

ProjectID	SubmittalDate	Comment
1	1/1/2024	Some Comment for Jan on project 1
1	1/2/2024	Some comment for Feb on project 1
2	1/2/2024	Some Comment for Jan on project 2

Would this be an appropriate place for a lookup field in the table, or should I only use that on the form which our staff will use?

Lookups are Evil in a table. Only use them on forms

The Access Web - The Evils of Lookup Fields in Tables

Also you can do a crosstab query. So although your data in inputed as above you can display it exactly like you show above in columns. It may take a little work, but easily doable.

 

[Missomissou]

Thank you! Working on this now.

 

[Missomissou]

Thank you! Working on this now.

Would you be able to point me to instructions on how to convert/import data from the format I have it in currently into Access with the fields set up as you suggested? I tried creating a pivot table in Excel, but that didn't seem to work. Then I cut and pasted some of the data into what I thought would be the correct long table format (with the field names in the left most column and repeating 12 times for each monthly update). But Access then tried to enter all the data into two fields (ID and Field 1--a name that it created for the second column of data). I've been searching the web for tips, but haven't found anything useful... yet...

 

[mike60smart]

Are you able to upload a copy of your database together with a copy of the original file for Import?

 

[Missomissou]

Are you able to upload a copy of your database together with a copy of the original file for Import?

I can upload the database, but it seems the attach files function below is not recognizing Excel files?

 

[theDBguy]

I can upload the database, but it seems the attach files function below is not recognizing Excel files?

Try zipping them up first.

 

[mike60smart]

Here you go! This zip file also contains an updated version of the DB (with the table I created to capture Monthly Update data, which I started populating manually via the associated form). Pardon the seeming duplicates in table names--it's been a busy day.

Hi
I only had a quick look but you have Autonumbers with a DataType of Text. Autonumbers should be LongInteger.
You are using Multi Value Fields when it is recommended that the multiple values selected should be records in a Related Table.
You are using Lookups in table fields. Google "The Evils of Lookup Fields in Access Tables"
You should not use any spaces in field names.