how to write this formula in Access?

[oozze]

Hi everyone,

I am pretty new with access queries. Mostly done alot with excel.

I need to know how to write this in access.

for example:
I have a table list of items and my specifically interested to pick up item description that have "power' in it.

So, in excel, I wrote:
+MID(A14,FIND("Power",A14),18)

How to translate this in access?

Thank you in advance.

oozze

 

[jdraw]

A query along these lines

Select * from YourTable
Where YourField Like "*Power*"

 

[oozze]

Hi thks for the reply.
I forgot to add that I have used like "*power*" function on criteria.
It listed the items but I also need to group it by JOBID with sum of cost.

It didnt do the sum.

unless i missed something.

SELECT [12 Material-Filter].JobID, Sum([12 Material-Filter].COST) AS SumOfCOST, [12 Material-Filter].[Item Code], [12 Material-Filter].[Item Description]
FROM [12 Material-Filter] LEFT JOIN Job_list_all ON [12 Material-Filter].JobID = Job_list_all.JobID
GROUP BY [12 Material-Filter].JobID, [12 Material-Filter].[Item Code], [12 Material-Filter].[Item Description]
HAVING ((([12 Material-Filter].[Item Code])="ee") AND (([12 Material-Filter].[Item Description]) Like "*power*"));


This is my query as of now. didnt do the grouping sum.

Regards,

oozze

 

[plog]

Looks good to me. My guess is you don't fully understand what GROUP BY (http://www.w3schools.com/sql/sql_groupby.asp) does.

Can you demonstrate what you want with data? Provide two samples:

A. Starting sample data from [12 Material-Filter] and [Joblist_all]. Include field names and enough data to cover all cases.

B. What you expect your query to return when you feed it A.

Also, you have chosen poor field/table names. You should only use Alphanumeric and underscore characters and only letters to begin any names. I would rename your tables/fields to exclude spaces, dashes and any other non alphanumeric characters.

 

[oozze]

[12 Material-Filter] would looks like
JOBID /Cost /Item
1 / 20 / (aa Power)
1 / 30 / (bb Power)

end result should be:
JOBID Cost
1 50

Right now, with the query i have the result is:
JobID Cost
1 20
1 30

[Joblist_all] has not come into the picture yet. I had the table linked but not used yet.



Regards,




oozze

 

[plog]

Using your starting data, the below query will produce the results you listed:

SELECT JOBID, SUM(Cost) AS TotalCost
FROM [12 Material-Filter]
GROUP BY JOBID;

When you realize its not what you want, please see my initial post and follow those instructions more carefully.

 

[oozze]

Using your starting data, the below query will produce the results you listed:

SELECT JOBID, SUM(Cost) AS TotalCost
FROM [12 Material-Filter]
GROUP BY JOBID;

When you realize its not what you want, please see my initial post and follow those instructions more carefully.

I retraced my query again and tried to understand your initial post. I still dont get it.

What i am seeing is that "like "*power*" finds all rows that consist the word "power". But, when i sum the cost, the cost didnt know that they have to add all cost that consist "power" since item description isnt the same, such as aa power, bb power, cc power and etc.

So, if this is in excel,
I would create another column using the formula that i wrote: +MID(A14,FIND("Power",A14),18).

This formula will collect the word "power...." from the original item and leave others blank.

Then, I will do the group using this column instead of original item description.

However, I am having difficulty writing the similar formula in access.

 

[oozze]

ah i fix it. I should have unclick DONT SHOW...