Access Query - is it possible?

[whatazoo]

I have never worked with Access until the last six months. Our business has used Filemaker Pro for years, but it just got too expensive for the few people that used it. So, I was tasked with finding a way to make what worked there, work in Access. I mostly have it working in Excel, but the end users really want something similar to what they had.

A little back story so when I ask my question it hopefully makes sense. We have about 4-5 staff working with 25-100 clients per fiscal year (July to June). We are a non-profit social services provider. Among other things, this database is for the people who help people find jobs and support them after they have a job. We are funded by the state 100% in this department, so we get "Purchase Orders" from the state for a client for X number of hours for X type of service. Staff need to track all hours worked (.25 hour increments) by client, date, staff and notes for each thing they do for a client. They then need a "hours left by client" for the month - preferably also by type of service.

I need to know if this is doable? I can get the notes, date/time and summary for the month - pretty basic stuff I would guess. I cannot figure out how to subtract the ongoing summary from the allocated hours for the month. I have a power query for the subtraction in Excel (by total hours not by type).

I have a database that has a table for Clients.
1. Would like to have a "yes/no" to show if they are active or not, Filemaker I had a script to only show active clients when they opened their database. But not a deal breaker. I have the yes/no, but nothing filtering yet.
2. I have a few fields here, calculating the first/last name, Lookup for a Counselor
I have tables for Counselor, Staff, PO Type (service type) for lookup purposes.
I have a table for FY21 hours
1. This table looks up info from Client and PO Hours type
2. I have a month/year field and a field for the number of hours the PO allocates.
I have a table for the overall data - lookup for Client Name, Staff, and Service Type. Typically, staff will have a note - they have hours by note. Sometimes, that number is a zero - meaning less than 7 minutes probably.

I have a query on my data table that summarizes hours by month by client. I have it so I can narrow that down by the hours type and it does group it when i run it.

I just can't figure out how to subtract the information. I have about 9-10 different service types. Most are blank right now as I am just testing. I'm not sure what else I need to tell you? Any help would be appreciated.
Stephanie

 

[Micron]

I need to know if this is doable?
yes

I have a database that has a table for Clients.
1. Would like to have a "yes/no" to show if they are active or not, an InactiveDate date field is better. If null they are active, if not they are inactive AND you now now when that occurred. Yes,no fields should be kept to a minimum.
Filemaker I had a script to only show active clients when they opened their database. But not a deal breaker. I have the yes/no, but nothing filtering yet.
2. I have a few fields here, calculating the first/last name, Lookup for a Counselor do not store calculations, do not combine like data
I have tables for Counselor, Staff, PO Type (service type) for lookup purposes. Isn't a counselor considered to be staff? If yes, then one table. Type is a reserved word and should not be used for objects.
I have a table for FY21 hours
1. This table looks up info from Client and PO Hours type don't use lookups in tables
2. I have a month/year field and a field for the number of hours the PO allocates. POdate as full date. Again, don't combine like data and don't split dates

Your biggest handicap is that you know Excel and will probably let it influence your Access design choices. They are as alike as a knife and scissors but you wouldn't try to cut a steak with scissors would you? Do yourself a BIG favour and do some research. Above all else, you must understand db normalization. Here's my whole enchilada for a start. Suggest you go through it all and find your own sources if some of these don't speak to you. Then maybe post back with your revised table design ideas.

Normalization Parts I, II, III, IV, and V

What Is Normalization, Part I

Thoughts, opinions, samples, tips, and tricks about Microsoft Access

rogersaccessblog.blogspot.com

and/or

Database Normalization – Holowczak.com Tutorials

holowczak.com

Entity-Relationship Diagramming: Part I, II, III and IV

Entity-Relationship Diagramming: Part I

Thoughts, opinions, samples, tips, and tricks about Microsoft Access

rogersaccessblog.blogspot.com

How do I Create an Application in Microsoft Access?

How do I Create an Application in Microsoft Access?

Thoughts, opinions, samples, tips, and tricks about Microsoft Access

rogersaccessblog.blogspot.com

Important for success:
Naming conventions - General: Commonly used naming conventions

MS Access Naming Conventions

_________________________________________________________ Naming Conventions. This document is aimed at the user who is unfamiliar with any naming convention. It is based upon personal experience and the interaction I have had with others. It is not a hard and fast rule. Why use a...

www.access-programmers.co.uk

What not to use in names
- Microsoft Access tips: Problem names and reserved words in Access

About Auto Numbers
- UtterAccess.com
- General: Use Autonumbers properly

The evils of lookup fields - The Access Web - The Evils of Lookup Fields in Tables
Table and PK design tips - Microsoft Access Tables, Primary Key Tips and Techniques
About calculated table fields - Microsoft Access tips: Calculated Fields
About Multi Value Fields - Multivalued Fields - Mendip Data Systems

 

[whatazoo]

Thank you, you are correct. I know enough about Excel to be dangerous and think Access should be Excel on steroids or something. I will do some research and come back if I still have problems.

 

[Joe4]

I know enough about Excel to be dangerous and think Access should be Excel on steroids or something.

That is a common misconception. Access is NOT Excel on steroids. It is an entirely different program altogether.
Access is a relational database program (like SQL or Oracle), though it has more robust interface features.

While there are some things you can do in both Access and Excel, they both really are designed for different purposes.
For example, many people create databases in Excel, though they are typically very clunky and it takes a lot of additional programming to try to do what Access can do natively.

 

[zweifuss]

MS Access may not be the best programm for this, but it is definitly doable. Have you considered to use open source solutions like eHour, Kimai or OpenProject?

I have a database that has a table for Clients.

Great. Create also one for users (maybe use table "Staff") and one for time tracking. Use linked tables to connect to that database. Use Access local tables only for development and testing.

1. Would like to have a "yes/no" to show if they are active or not, Filemaker I had a script to only show active clients when they opened their database. But not a deal breaker. I have the yes/no, but nothing filtering yet.

Access filtering of tristate bit/boolean type fields is a bit difficult. If the field can not be Null/Empty (recomended), then you can filter with True and False as well as with 1 (equals yes) and 0. "Yes/No", "On/Off" or a checkbox is more or less output formating, unless you use a field with a string/text type.

The AutoExec macro, if present, is called at the start of the database. It can open a form in a hidden state (maybe call it "Session"), that sets/unsets that bit on form_load and form_unload . I recomend to scheduele a job, that sets all staff to away at night.

Code of form_Session may look like this:

Option Compare Database
Option Explicit

Private usrName As String          ' Local account name

Private Sub Form_Load()
    usrName = Environ("USERNAME")  ' A bit risky, because users can edit this. Use NTLM or WMI for production.
    setLogonState True
End Sub

Private Sub Form_Unload(Cancel As Integer)
   setLogonState False
   DoCmd.Quit                      ' Close database.
End Sub

Private Sub setLogonState(LogonState As Boolean)
' Assumes local or linked table 'Staff' with text field 'AccountName' and bit field 'isOnline'.

    With CurrentDb().CreateQueryDef("") ' Temporary query definition
        .SQL = "UPDATE Staff SET isOnline = [pState] WHERE AccountName = [pName];"
        .Parameters("pState").Value = LogonState
        .Parameters("pName").Value = usrName
        .Execute dbFailOnError
        .Close
    End With
End Sub

 

[xenou]

Its certainly do-able. It may be frustrating (as mentioned above in a few different ways) that you will have to learn some principles of database design and basic SQL in order to effectively use Access. However, that hasn't prevented hundreds of thousands of people doing all kinds of crazy things in Access (and this wouldn't even be a crazy thing - just a perfectly normal type of thing for Access!).