Finding records with a value <0.8 of AVG in a mixed dataset

I'm not at all confident in SQL, simple selects and inserts is about it....however, I have a requirement to do the following:

I have a table in Access with airline tickets listed. There's a FromCity, ToCity, CurrentPrice, FlightDate and lots more irrelevant fields. What I'm trying to create is a query that will show me these 3 fields for each individual ticket that has a CurrentPrice < 0.8*AVG(CurrentPrice) for it's combination of FromCity / ToCity on each day in the entire dataset.

I could VBA it and bring back each combination of From / To in turn and a run a query on each one.....but I'm hoping there's a way to do this in one query in SQL. Oh and results are coming into an Excel page so I'm ADOing it and running SQL on the Access table from Excel VBA rather than using an Access form.

Even pointing me a direction to start in would be huge assistance here. My SQL is terrible.

Instead of trying to write SQL, why not just use the UI in the query builder? If you post some sample data in an Access DB, I may be able to help by building it in the UI and then copying the SQL statement that is generated. Do it the easy way and use the tools available rather than the hard way.

Administrative Note:

We would very much like to help you with your query, however the thread title does not really convey what your request is about. Tell us what you are trying to do, not how you think it should be done.

Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

(Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)

I have created two queries. One to calculate what 80% of the Average ticket price for each leg and the second to compare that number with each ticket sold.

First query:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

That is more complicated than I have time to figure out. A suggestion that you may want to explore is to build your query with a parameter for the date and then try and modify the queries I provided so that it is for a specific date. With each iteration of dates, you can export your data to Excel and save it so that in the end, you will have 365 query results in an excel file that you can analyze.

Did a little bit of work. Here is a revamp on the first query. Cannot get second one to work yet.

Please Login or Register  to view this content.