Hello there. I'm a student in Information Technology Management and am working on an assignment for Access 2007. I ran into some issues with creating my queries and cannot seem to get the right results. Can anyone help?

Yes, someone, somewhere can help. However, we cannot, as you didn't provide any information about your issues. There are many many questions to be answered here. Those first requiring information extraction (like pulling teeth) tend to be passed over.

*begin extraction... *

I have a simple database. I need to create a query that will show items that yield the most revenue. I have fields with ItemName, ItemType, PricePerItem and DailyAverageSold. I need to create a calculated field showing "DailyAverageSold * PricePerItem".

What did you try?

I have put all these headings into fields. Then I added a new field called - Expr1: [RevenuePerItem] - and in the criteria put in the formula [DailyAverageSold]*[PricePerItem]. But when I run the query - I get the pop up 'Enter Parameter Value' - I don't know what to do with this.

That is the behavior that normally results when there is a reference Access cannot resolve typically caused by a syntax error. In this case the reference is [RevenuePerItem] which doesn't exist in your table. The syntax for calculated fields is: FieldName:Formula placed in the "Field:" field in the query design.

The "Criteria:" field is the wrong place to put the correct formula. Using the syntax above, use this formula in the "Field:" field in the query design. This is covered in Access Help (F1) > Queries > Introduction to queries > "Make calculations based on your data" subtopic. The help is your friend.

Attached Images

 

As Scleros said, you put the calculation after the Alias in the top row of a blank column. The next step is to sort that column in descending order so the that top revenue items are on top.

Ok - thanks - the query is starting to look right. My next problem. I need to include criterion to select text. I need to tell it to select either a 'yes' or 'no' from a field. I also need to select 2 categories - in my specific query they are items are are 'bread' and 'pastry'

Please review the guidelines on asking for help with homework that can be found here:

Ask Me Help Desk - Announcements in Forum : Arts & Literature


We will NOT do your homework for you. You need to show us what you have done (post the SQL from the query) and we can critique your work.

Sorry - I didn't mean to come across as trying to get you to do my work for me. I don't know how to post SQL. And the Help in access is not that 'friendly'. I find that it just gives too much information and it's vey difficult to figure out. I was able to get the answers by putting - RevenuePerItem: [PricePerItem]*[DailyAverageSold] - into the field. That showed up correctly. I have values as Yes and No in the 'MadeInStore' and get the error of mismatching data. I've also tried to put the 'yes' in the criteria but when I run the query it shows up as -1 or 1.

Yes, it sucks, but unless you go buy an Access book, it's the only thing you've got. And I don't think you learn anything productive about how to use Access for real applications spending all your time reverse engineering the syntax through trial and error. I've been down that road and I still get frustrated every time I do something in Access since I don't use it everyday.

Data in Access is of some type. You set the type in the table design - number, text, etc. There is a Yes/No type that equates to a True/False or BOOLEAN data type that is common in programming languages. Your Yes/No values could also be implemented as "Yes" and "No" strings of type text. When specifying criteria, the type of the selectors in your expressions should match the type of the values in the data or you can get funky results. Yes/True/On and No/False/Off are equivalent when entered without quotes and can be used for BOOLEAN expressions.

Posting SQL simply means posting the SQL statements such "SELECT Table1.ItemName FROM Table1 WHERE (((Table1.ItemName)="Widget"))". This format for the query is shown via SQL View in Access. You may not have reached the point in the class where SQL notation is discussed. The Design View of Access is supposed to be a simpler way to construct queries than having to type in the SQL directly and it generally is as long as one gets the @#$%^ syntax correct and the expressions in the right boxes.

Thanks for all the help. Since this assignment is 5% of the total mark, I've decided that I'll settle with part marks for what I believe could be the correct answers (for which I put the calculation in the 'field' lines). I'll give up now. Funny how Access has changed since 2000 when I first learned it in college (and the Access book came with the course back then). Now everything is online - and here I thought that it would be easier! Ha! Anyway - thanks again. It's been a learning experience for me. Until today, I never thought to look for a website like this. Maybe we'll connect in the future on some other thread. Take care.

In Query Design mode, you access the SQL view of your query. Just copy and paste. As to the Access Help being friendly, I think it very useful. I use it frequently to remind me of syntax.

But I can't believe you were given a school assignment without first having query design explained. Or you were at least given handouts that explained it.

Did your instructor explain datatypes to you? That would be taught before queries, as part of table design. You apparently have a Yes/No (Boolean) field. Such fields contain the value -1 for Yes and 0 for No. If you enter "Yes" (with the quotes) as your criteria, you are comparing a text datatype with a Boolean datatype. Hence the mismatch. You can use Yes (without the quotes) or True or On (No, False and Off) or you can just use -1 and 0.