Query Criteria not pulling properly (1 Viewer)

[araza123]

Hello all,

Excuse me in advance as I am very green with access. What I am trying to do is get my query to only show results if the filed name contains one or the other name I am specifying

I presently have it as.

Criteria: Like"*Honda*"
Like "*ford*"

the logic only pulls in ford right now. How do i get the query to pull both in? Thanks in advance!

 

[Gasman]

Remove the >200 criteria

 

[araza123]

Remove the >200 criteria

wow easy enough, but if i needed to add a number filter in that field. Say i only want to see numbers less then 20 in the gross admin 1 field

 

[Pat Hartman]

You would have to repeat the expression on each row to which it should apply.. What you have now, applies the number to only the first row. The way the criteria grid works is criteria in a single row is AND'd and criteria in different rows is OR'd.

Where (fld1 > 200 AND fld2 Like= "Honda*")
OR
(fld2 = Like "Ford*)

PS, You should add a manufacturer to your table so you don't have to use wildcards to search on similar names. They prevent the query engine from using indexes which wouldn't matter if you had only a hundred rows of text data but could be a real buzz kill if you had half a million rows. Always design for the most efficiency. Even if it doesn't matter now, it could in the future so add the group field so you can use "=" rather than "Like" as the relational operator.

 

[araza123]

Hmm i think i understand. So right now this will not pull in gross admin 1 numbers less then 20? totally understand your manufacturer table as well!

 

[plog]

You need to normalize your data. When you start to suffix field names with numbers it means you need a new table.

Tables should accomodate data vertically (with more rows) and not horizontally (with more fields). That means instead of 7 [Gross Admin] fields in one record, you would have another table that holds that data in 7 rows.

I suggest you put this query aside for a bit and brush up on normalization:

Database normalization - Wikipedia

en.wikipedia.org

Read thru that, work on a few tutorials, then apply what you learn to your data.

 

[stardustvega]

I think that it's easier to understand what plog is talking about with an example and some general concepts.

Access is based on the *relationships* between data. For instance, a salesperson may make multiple sales. A dealership may have multiple salespeople, etc. Access makes it easy to keep track of those relationships. Here's an example of how.

In this very simple demo database, you can see that I've got sales and salespeople in two tables. Each sale involves a salesperson and a type of car.

Even though they're in different tables, I can easily find out what reps have sold more than 1 Honda, for example (this is one of the queries in my sample database) here. Generally, you want to do it the way I've shown instead of adding, say, a column in the sales table for each sales person.

When I was getting started, I found this video REALLY helpful in getting my head around it. In general, this guy seems to have the best tutorials I've found on Access if you learn well via YouTube.

 

[araza123]

I think that it's easier to understand what plog is talking about with an example and some general concepts.

Access is based on the *relationships* between data. For instance, a salesperson may make multiple sales. A dealership may have multiple salespeople, etc. Access makes it easy to keep track of those relationships. Here's an example of how.

In this very simple demo database, you can see that I've got sales and salespeople in two tables. Each sale involves a salesperson and a type of car.

Even though they're in different tables, I can easily find out what reps have sold more than 1 Honda, for example (this is one of the queries in my sample database) here. Generally, you want to do it the way I've shown instead of adding, say, a column in the sales table for each sales person.

When I was getting started, I found this video REALLY helpful in getting my head around it. In general, this guy seems to have the best tutorials I've found on Access if you learn well via YouTube.

thank you to you and plog for taking the time out and give me this info. I understand better now. So the gross admin buckets I have all mean different things, so they should not be combined in my case. In my data set my unique identifier is "contact ID" both tables will share this. So in my case say if i wanna pull in gross admin which is in one table and paid claim amt which is in another table, i should still be able to do so if i am correct?

example of my table below

 

[plog]

First, you should only use alphanumeric and underscores in names. That means no spaces or special characters. The table [GAP Sales] should become [GAPSales] and [c#Contract ID] should become c_ContractID, or something similar.

Second, use autonumber primary keys. They are the heart of creating relationships in a relational database--they are how you should link tables to each other.

So the gross admin buckets I have all mean different things, so they should not be combined in my case.

I don't know what you mean by "combined", but it seems you are arguing against a seperate table for them--that is wrong. You definitely need another table for all your [Gross Admin N] data. Actually, you need a new table for all your [X Admin N] data (where X is a type and N is a number).

So, from my first 2 paragraphs, that means GAPSales has a primary key called [GAP_Sales_ID]. You now create a new table to handle all that [X Admin N] data. It would have this structure:

AdminData
AdminData_ID, autonumber, primary key
GAP_Sales_ID, number, foreign key to GAPSales table
AdminData_Type, text, this will hold if the record is for Gross or Net (you now store this in a field name),
AdminData_Number, number, this will hold the suffix of each field name if necessary
AdminData_Value, ?, this will hold the value currently in each of those fields

That's it, those 5 fields in that one table will now house all your [Gross Admin N] and [Net Admin N] data currently in GapSales.

I fear by some of your field names suffixes (AVG_, Gross_, Max_) you might have used some calculated fields in your tables. That too is incorrect if you are storing the data that goes into those calculations sepereately from them.

 

[araza123]

First, you should only use alphanumeric and underscores in names. That means no spaces or special characters. The table [GAP Sales] should become [GAPSales] and [c#Contract ID] should become c_ContractID, or something similar.

Second, use autonumber primary keys. They are the heart of creating relationships in a relational database--they are how you should link tables to each other.



I don't know what you mean by "combined", but it seems you are arguing against a seperate table for them--that is wrong. You definitely need another table for all your [Gross Admin N] data. Actually, you need a new table for all your [X Admin N] data (where X is a type and N is a number).

So, from my first 2 paragraphs, that means GAPSales has a primary key called [GAP_Sales_ID]. You now create a new table to handle all that [X Admin N] data. It would have this structure:

AdminData
AdminData_ID, autonumber, primary key
GAP_Sales_ID, number, foreign key to GAPSales table
AdminData_Type, text, this will hold if the record is for Gross or Net (you now store this in a field name),
AdminData_Number, number, this will hold the suffix of each field name if necessary
AdminData_Value, ?, this will hold the value currently in each of those fields

That's it, those 5 fields in that one table will now house all your [Gross Admin N] and [Net Admin N] data currently in GapSales.

I fear by some of your field names suffixes (AVG_, Gross_, Max_) you might have used some calculated fields in your tables. That too is incorrect if you are storing the data that goes into those calculations sepereately from them.

Thank you Plog, so for my tables you are saying i should remove the admin and data and create a totally new table for that? so i would then i have 3 tables? One that is for that and the other 2 which is GAP sales and GAP Claims. Sorry for my ignorance has im very new at this.

 

[plog]

Yes. Prior to that, and more importantly, I said you should read up on normalization. That will help you understand the underlying fundamentals of of setting up tables so that you can spot other errors you have made with your tables that I have not spotted.