• 0

MS Access Help!


Question

I'm wondering if any MS Access experts can help me with this problem. I haven't used Access since my college days and completely forgot how to do certain things.

 

My database is used for logging children that attend certain classes, it records their details: date of birth, name, medical info etc..

 

I need to be able to generate a report based on their age, so that all children running the report will generate a list of all children under the age of 8 and another report that generates a list of children over the age of 8. How complicated is it to do this based on their DOB? I guess I will need to work out their age from they DOB and then filter from there?

 

I've tried generating a report but can't seem to get desired results, would i work from a query first? how would the criteria look?

It would be even better if i could have over and under 8s on the same report, but just have them separated so they are easily distinguished.

 

Is this possible? thanks

Link to comment
https://www.neowin.net/forum/topic/1234267-ms-access-help/
Share on other sites

4 answers to this question

Recommended Posts

  • 0

I'm no Access expert sadly, however i've been using it a bit recently so can half help you with this. I can workout the age each person will be this year, however i'm unsure how to take in to account if the person has had their birthday or not.

 

Example Data:
 
2rf51cx.jpg

Query:
 
Create a query and add the Child's Name, Dob and any other data you want in the result.

Then in an empty filed enter the expression Age: Year(Now( ))-Year([DoB])
 
This subtracts the person's birth year from the current year to give the persons age, however as I realised when doing this it doesn't take in to account if the person has had their birthday or not yet.
 
2akfbqo.jpg

 

For "Criteria" you can simply filter the results, so <8 will return any person who is younger than 8

=8 would be anyone who is 8

Then >8 older than 8

You can then create reports based off the results of the queries:

 

25rjvgm.jpg

 

I hope that gets you started in the right direction anyway. Ill try have a look tomorrow and see if I can work out the persons exact age.

  • 0

Use the DateAdd Function. It will be more precise.

 

WHERE DateAdd ("yyyy", 8, DOB) <= NOW

WHERE DateAdd ("yyyy", 8, DOB) > NOW

 
 

You'll get the date the child turns 8

If it is less than or equal to NOW() it means he/she has already turned 8

If it is greater that NOW() it means he/she hasn't turned 8

 

 

  • 0

I have another question:

 

Is it possible to set some criteria when a report is run? As above i need under 8s and over 8s, but these are also split into 2 separate groups .. so in total that is 4 queries that can be run

 

morning: over 8s and under 8s

afternoon: over 8s and under 8s

 

Id like it so that when a report is run it asks if them if they want the morning and afternoon group and then asks them if they want over8s or under 8s and then shows results accordingly?

This topic is now closed to further replies.
  • Recently Browsing   0 members

    • No registered users viewing this page.
  • Posts

    • This is what I assumed would happen back when the rumors started. A new tab on Instagram for your text feed...
    • I agree - it's Cerebras power. Funny how Mistral isn't listed for that provider on OpenRouter despite Mistral being on OpenRouter. Edit: Ah, Mistral is of course their own provider on OR... Which probably in turn uses Cerebras. It's just that you don't get to it directly via OR.
    • I’m all for autonomous taxis. In my rural area a car is vital for work, socialising and appointments. Public transport is almost non-existent, with only two buses a day and none on weekends. The roads are narrow, unlit B roads with no pavements, so walking or cycling is unsafe. If you cannot drive, you depend on lifts or expensive taxis. City politicians / Gov keep making car ownership harder: higher road tax, soaring insurance because of repair costs and parts delays, and ever-rising fuel prices. A single mistake, such as a shaky cyclist overtake or getting trapped in a yellow box due to badly placed traffic lights at a roundabout and someone pulling out in front, can cost you your licence. They also push everyone toward electric vehicles, which do not work here. Many houses have no driveway and due to historic protection rules/laws cant add one, and you cannot run charging cables over the few bits of pavement we do have. Driving elsewhere to charge would also hurt the few local businesses left and act as a hidden tax on rural life as its more expensive to charge away from home. That’s why an on-demand, self-charging automated taxi service would be perfect for people here.
    • You can get deeper bass from your Polk soundbar with this $99 wireless subwoofer deal by Sayan Sen JBL and Samsung are still selling their soundbars with wireless subwoofers at great prices at the moment. The former has its BAR 1000 and BAR 700 models at their lowest, which the latter has the Q900F, Q800F, and Q600F, at least prices. If, however, you already have a soundbar, like the Polk Audio React, say, then you can add a subwoofer and get deeper bass; and now is the best time to pick one up as the React wireless sub is on sale for just $99 (purchase link down below). Unlike what some may feel like, a subwoofer is not there to make the bass louder, well technically yes, but in the way that makes your system boom more, that's what is known as the "boomy bass" and that is not something desirable, although some do like it. Instead, the subwoofer is supposed to augment the low end of your main speakers such that the bass/sub-bass from the subwoofer naturally blends with your main speaker's mid-bass/bass. The React subwoofer does not list too many spec details but Polk Audio claims that it can go down to 25 Hz. The unit comes with a high excursion long throw 7 inch bass driver and even with high excursion (high Xmax), it is never easy for a 7 inch driver to dig so deep. Since Polk does not quite disclose the SPL (sound pressure level) of the product at this 25 Hz frequency, it is probably safe to say that the dB output in this deep bass range is going to be much lower than its average SPL at frequencies like the 40-80 Hz spectrum. It is worth noting that most of the music actually lies above the 30 Hz range and thus if you are a music listener, the low output below 30 Hz won't even be noticeable. For movies, this sub should deliver plenty of oomph, and you will only miss the deepest of rumbles, but for just $99, can you even complain on missing out the lowest octaves? Polk React subwoofer rear view showing IO panel Get the Polk React 7 inch subwoofer at the link below: Polk Audio React 7" Wireless Subwoofer - Bass Adjust EQ, Amazon Alexa Compatible: $99.00 (shipped and sold by first-party Amazon US) (MSRP: $199.00) You can also check out other subwoofer options on Amazon at this link. This Amazon deal is US-specific and not available in other regions unless specified. If you don't like it or want to look at more options, check out the Amazon US deals page here. Get Prime (SNAP), Prime Video, Audible Plus or Kindle / Music Unlimited. Free for 30 days. As an Amazon Associate, we earn from qualifying purchases.
    • Just merge it with Instagram already... Meta having two platforms with overlap was a bad business decision.
  • Recent Achievements

    • One Month Later
      Stokenking earned a badge
      One Month Later
    • One Month Later
      Kevin Jones earned a badge
      One Month Later
    • Week One Done
      Kevin Jones earned a badge
      Week One Done
    • One Month Later
      henryj earned a badge
      One Month Later
    • Week One Done
      henryj earned a badge
      Week One Done
  • Popular Contributors

    1. 1
      +primortal
      527
    2. 2
      ATLien_0
      270
    3. 3
      +Edouard
      199
    4. 4
      +FloatingFatMan
      190
    5. 5
      snowy owl
      137
  • Tell a friend

    Love Neowin? Tell a friend!