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.

[@yebelaygetnet5990]

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

[@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

[@SixMinutesSmarter]

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

[@smmng]

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

[@ahmedaamri1290]

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

[@kerrieleong4801]

Thank you Ralph. Very interesting tutorial.

[@danishharoon]

Thank u have my IGCSE finals tommorow alot of help

[@thelemleys]

Thank you Ralf This very helpful 🙏🙏

[@nasirayub951]

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

[@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

[@r_b5951]

Great set of Access tutorials. Thanks Ralph!

[@InfinityShakes]

Thank you. Helped me with my assignment for class.

[@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 ..

[@yash1152]

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

[@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.

[@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

[@mybitch2700]

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

[@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!

[@lamunugloria4653]

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

[@emilynhg9959]

do you have a executable query for this tables?

[@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.

[@momalade8626]

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

[@franciscoxcamposp9984]

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

[@Ubsofter]

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

[@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.

[@GO-gh2cp]

very usefull ! thanks !!! 👍

[@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%

[@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.

[@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

[@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.

[@marvin-sw9rv]

how do filter field in tables

[@OTLEminem]

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

[@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.

[@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

[@mohammadsamikhan5300]

Good

[@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".

[@ArifRahman-ex2vx]

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

[@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???

[@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.

[@AdamFente]

Thanks dude this helped out a lot

[@behindthescene2727]

will watch later

[@dukestt]

never stop the learning...

[@ekkhitty4175]

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

[@GabrielHernandez-il1uf]

Hero

[@ravikishorergajavalli]

Thanks Ralph Phillips

[@filipnovak1751]

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

[@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

[@ThisMightBeBenjaminYoung]

how was yoga class?

[@SixMinutesSmarter]

Very good.

[@iBasskung]

Thank you so much.

[@Riders9191]

Thanks Mann..

[@anilvaljibhanushali5353]

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

[@kimbby]

thank you so much

[@ArifRahman-ex2vx]

Thank you sir

[@mzz7768]

Thanks

[@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?!

[@mabalamakhakhe42]

Hope you enjoyed yoga

[@SixMinutesSmarter]

Always

[@michaelliolios1679]

bro dont be missing yoga

[@alahatim]

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

[@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).

[@cushcush100]

Holy fuck,... blurry!

[@patstechwritingtips8418]

Yoga? Seriously?

[@Urketadic]

Yoga class huh

[@stevenstocki3122]

Yoga dude ? Really ?

[@marissa954]

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

[@d.w.1805]

grow up