Create Queries with Calculated Fields and Aggregate Functions

[@SixMinutesSmarter]

I teach Office applications and Web Development at a community college in Oregon. I'm self-taught in Office apps. Had to learn on the job after college when I worked for an insurance company (I've got a Business/Finance background). Later, in '97, I started learning HTML to make web pages. Thanks for watching.

[@t0rtill0s]

Ralph Phillips thanks for you post, this is considered learning on the Job for me.. awesome videos!

[@Jdawg2theMaxx]

Thank You So Much!!! Been stuck on a problem like this for hours with no idea how to do it! You helped me get through it in less than 10 minutes.

[@jamesedwardsjr3466]

Thank you Ralph for the video tutorial; you helped jump start the learning curve in writing expressions; and the zoom function tip improves visibility to write out the syntax. I look forward to watching more of your videos pertaining to access and working with SQL's.

[@TheBrilliantPhoenix]

Ralph has a very good voice for instruction! Unfortunately, the Office version is several generations past, and Access has changed significantly in the meantime.

[@SixMinutesSmarter]

It's amazing how many cool ideas you'll have when you're supposed to be clearing your mind.

[@ricksegalCanada]

This was super helpful and, after six years, relevant and very much appreciated! Thank you

[@DaveBernstein2023]

i have been searching for the solution to the same problem for months and now this guy save me

[@danishharoon]

Thank u have my IGCSE finals tommorow alot of help

[@kerrieleong4801]

Thank you Ralph. Very interesting tutorial.

[@yebelaygetnet5990]

nice video .......be continue !!!1

[@mybitch2700]

Awesome thanks! This will most likely help me get through my Admin Prelim tomorrow, haha! ;)))

[@nasirayub951]

Dad Ralph Phillips, because you are like my dad .thanks you are helpful

[@smmng]

I am very new to access and in the process of making a database - thanks - helped a lot

[@ahmedaamri1290]

ألفين وتماميه

[@yash1152]

i think this will help me too.... (was preparing query for marks wise distribution of topics, grouped according to unit they belong to).

[@thelemleys]

Thank you Ralf This very helpful 🙏🙏

[@r_b5951]

Great set of Access tutorials. Thanks Ralph!

[@sinisterem]

Hi, useful video. I have a bit of a noob-question: how do I create a field that calculates the sum total of a column? Goal is to have query from wich I can create a two column report: colomn a: expanses, column b: amount. And one field with the sum total of all the expenses.

[@InfinityShakes]

Thank you. Helped me with my assignment for class.

[@salmashaik5676]

thank you mate u helped me with my exams

[@slyowusu99]

question. what is the best way to create an Annual leave booking simple DB on Access 2003? For a few staff members to ensure we all do not take leave at the same time. thanks

[@lamunugloria4653]

Thanks a lot, when do formulas come in use and which ones are the only recommended signs / or formula ones to be used?

[@nicoled9765]

Can you suggest on how I would create a querie and report in Access of all employee work schedules for the week? I was hoping to make the report look like a table with the days of the week across the top/columns and hour increments along the left side/rows and all the employees scheduled for those times and days are in the table's center. Thank you!

[@nidalalahmad3421]

Hello Ralph, how can I create a query show a record depending on max value in this record? I mean show the max value and other fields in the same record.

[@stewmaker8685]

HELLO , is there a formula for determining if gender of a list of names that has the Mr. and mrs. initials. so that the outcome will be either M or F according to the initials on the names

[@elissarrez3462]

Hello , I have question : I am creating a Query, and I precised each client if he is a "M" or "Mlle" or "Mme". How can i count how many M's ; Mme's and Mlle's i have ? Thank you !

[@tutsecret499]

I want to count or sum of lab test 1 neg, 2 pos. So the column that has the tests shows all the results. What's the formula? =Sum[Test1]? I want to count all the 2positive, and the 1 neg. So How do I set up? =Sum[test1], 2. Thanks.

[@behindthescene2727]

will watch later

[@michaelle3743]

Problem: Add criteria to this query to return only the records where the value in the Credits field is less than 90 and the value in the Classification field is Jr or Sr. Is the answer: "Jr" in criteria, "Sr" in or, >90 in Credits Criteria ? I keep getting the wrong answer on my test.

[@momalade8626]

Thank you so very much. Just what I was looking for. You are awesome!!!!

[@modd1970]

Thank you Ralph for the video. If I have a customer with multiple purchases, how can I display the latest purchase ( only ) in a query ? Thanks ..

I love your presentation here sir, I have a problem with this calculation, to calculate unit price sale with the quantity and have my total balance please help

[@GO-gh2cp]

very usefull ! thanks !!! 👍

[@goodgirlteccie19]

How do I display values of a field in a query? I tested and i never got that one because it didnt make sense to me

[@mohammadsamikhan5300]

Good

[@franciscoxcamposp9984]

Hi there! Could help me with an specific exercise? Please, thanks so much

[@jamesedwardsjr3466]

Any ideas on how I can go about writing an expression in the query to add the total Charge amount based on the date for the month? Below is a snapshot of the the three columns available, and the Monthly ChargeAmount to be added. ClientID | TransactionDate | ChargeAmount | Monthly ChargeAmount

[@tutsecret499]

So what's the formula to count all xrays with the data Yes(did xray), No(did not). So it would be Sum or count =sum([XRay]), Yes or True. I am lost.

[@emilynhg9959]

do you have a executable query for this tables?

[@dukestt]

never stop the learning...

[@PMJS15]

So i need to create new field with a new name that will calculate and display the donation amount and it saying i need to make a function that shows it adds 50%

[@ravikishorergajavalli]

Thanks Ralph Phillips

[@GabrielHernandez-il1uf]

Hero

[@OTLEminem]

what the calculation if I want to have the balance more than 0 and less than 1000?

[@guitartrip1]

great video. I have been spending days on how to get a percentage of customers that are past due. Would you know how to get percentages on the query?

[@reannan1462]

Export it to Excel and calculate the percentage there???

[@FingCm]

thanks Ralph this really helped me

[@marvinadhikari9751]

That is informative, but what if I want to know total debt outstanding? Or, sum of column total?

[@_anon464]

+Marvin I wasn't following this that closely but if I'm understanding your question correctly, I think you'd just have the "debt" column only and change it's totals line to "Sum".

[@AdamFente]

Thanks dude this helped out a lot

[@josephburt-millerjr4934]

This is what I'm trying to do: I need to run a report based on proficiency dates for 120 days out, 30 days out and LATE. The proficiency dates are needed to my service 60 days prior to the proficiency date, so the 120,30,and Late would be calculated from the proficiency date. so basically if a prof. date is 1 Oct then they are due to my service 1 Aug and need to start showing up on a report 1 Apr, 1 Jul, and 1 Aug. Once they have been received for the current calendar year I would need them to go to the next Calendar year. So received for 2014 doesn't show back up on the report until 2015. So far I've created a table within Access 2013 from an Excel spreadsheet. it includes first name, last name, middle, proficiency date, and occupation number. So I'm thinking my next step is to create a query based on the proficiency date, but I'm not sure how to get those results to show within that table, or should I have to display in a new table? Would I have to create a separate query for 120, 30, and late? This table currently has over 450 entries and would need the ability to add/remove records when necessary

[@robynpearson5440]

hi i need to find out how do i create a query which i have to displybasically the make and model of cars which are purchased before a certain date please help

[@MichaelSmith-ox2sw]

Assuming you have a single table (called tblCar) with the fields: Make, Model and PurchaseDate, you could write a basic SQL statement like: "SELECT Make, Model FROM tblCar WHERE PurchaseDate < #01/10/2017# ORDER BY Make, Model ASC;" This will return all Makes and Models that were purchased before January 10, 2017.

[@ArifRahman-ex2vx]

Sir I need a total accounting as a beginner student in access.

[@ekkhitty4175]

Can i have a LINK you used to create this data base from scratch

[@marvin-sw9rv]

how do filter field in tables

[@iBasskung]

Thank you so much.

[@Riders9191]

Thanks Mann..

[@ArifRahman-ex2vx]

Thank you sir

[@mzz7768]

Thanks

[@ThisMightBeBenjaminYoung]

how was yoga class?

[@SixMinutesSmarter]

Very good.

[@kimbby]

thank you so much

[@anilvaljibhanushali5353]

No Words For You, Thanks _______________1000000000000000.....n times

[@filipnovak1751]

Where did you learn all this stuff? I mean Access, Excel, and all programming languages? Do you work with them in your job?

[@mabalamakhakhe42]

Hope you enjoyed yoga

[@SixMinutesSmarter]

Always

[@michaelliolios1679]

bro dont be missing yoga

[@TheNerdsFromHell]

HOW THE FLY FUCK DO YOU CREATE A GOD DAMN TOTAL BY ADDING A PREVIOUS TOTAL! I need this for weekly hours and I have not idea how to use access

[@MichaelSmith-ox2sw]

That is what the Sum function was used for. It iterates through the data and adds the values recursively.

[@JIMN26]

Wow, what a bunch of irresponsible clients! Was this during the housing crisis?!

[@Urketadic]

Yoga class huh

[@alahatim]

Really? Yoga reminder pops up during video recording. Pleb.

[@cushcush100]

Holy fuck,... blurry!

[@patstechwritingtips8418]

Yoga? Seriously?

[@johnclifford1920]

Thanks for the help but please try to smack your lips less

[@SixMinutesSmarter]

Got it. :) That was for the ASMR fans.

[@yash1152]

@@SixMinutesSmarter Hey, can we group, but also show sub-items, like a tree-view where u expand things by clicking the arrow or anything like that?? Any hybrid way involving export to word or something maybe, but which is still more efficient than the crude way (i.e. manually doing it).

[@stevenstocki3122]

Yoga dude ? Really ?

[@marissa954]

i knew it i'll see a comment about it lmao

[@d.w.1805]

grow up