Help: queries or forms and which type?

[phineas629]

Hi all,

I am stuck on what to do. I'm pretty new.
Here is the situation:
I have volunteers visit clients on a near-daily basis.

• Each client is only allowed a set total number of hours within an AUTHORIZED DATE SPAN.
• i.e. volunteer 1 visits client A for 300 hours for the year. volunteer 2 visits client B for 280 hours for six months.
• Each volunteer has invoice for total hours visiting every two weeks

• Total number of HOURS for each client must be met but cannot be exceeded.
• Total number of visits for each client cannot be exceeded.

So far I have four tables with fields for which I’ve built relationships:
Volunteers


· Volunteer_pk
· volunteer_FIRSTname
· volunteer_LASTname
· DOB
· CITY
· ZIP
Invoice
· INVOICE_NUMBER_PK
· Volunteer_FK
· HOURS_USED
· VOLUNTEER_START_SESSION
· VOLUNTEER_END_SESSION
AUTHORIZATION
· AUTHORIZATION_PK
· INVOICE_NUMBER_FK
· AUTH_BEGIN
· AUTH_ENDS
· TOTAL_HOURS_AUTHORIZED_FOR CLIENT
Volunteers
· CLIENT_pk
· CLIENT_FIRSTname
· CLIENT_LASTname
· DOB
· CITY
· ZIP
I’ve made forms for editing my tables. How should I keep track of total hours visited? Should I move to Queries next or reports? Also, which kinds of queries or reports? I realize all databases are unique but I would love some advice from “your point of view.” I've tried hard to conceptualize but ideas would be lovely.
Access is pretty amazing and I haven't used it for long. I would love to keep learning this more.haha
Thanks in advance

[Stingaway]

I would first take a look at your tables and consider merging the volunteer and client into one table (say "users" for the sake of argument) and adding a separate table that consists of "user type". That way you aren't having to keep up with last name first name info in two separate tables - its cleaner in terms of normalization and could give you more options in the future with regards to adding user types.

Secondly I would move to queries - start by creating queries for retrieving the data you want to see. You can tweak them to get them just the way you want them and then use those queries as the datasets for your reports.

It makes it a lot easier if you want to add data to a report but do not want to add it to the table per se (i.e. truncations, "extra" stuff per record, etc) - you can just add it to your query and in turn put it into your report.

Hope this helps.

[rpeare]

Stingaway has given you some sound advice I'm just going to chip in that you don't want to store calculated values in your tables. If you record a start time and end time of a visit you do not need to store HOURS_USED in your INVOICE table.

Secondly you likely want to break your invoices into an invoice 'header' record if your volunteers can visit the same person or multiple people in the same 2 week period. For instance if Volunteer A can visit Client A, Client B and ClientC on the same invoice you would want an invoice 'header' that had the primary key (as you have it) the volunteer ID, the period start/end (unless you're storing this in a separate table with a primary key itself) Then in your invoice detail have the line items for clients visited, dates visited and start and end time.

[phineas629]

Thanks guys,

I will look to normalize it some more. Would I then be looking to use a query to put in data into my invoice. I have to admit that I'm kind of confused because so far it seems that forms can do most my work for me versus inputting complicated queries. I'm not even thinking about reports anymore.

[phineas629]

I assume I could use an append query to input repetitive data (volunteers usually visit same hours per day) versus filling out individual forms, correct? If that's the case, should I be looking to make an append query? Now it seems work's going to be a bit more complicated before it gets easy.

[rpeare]

if you are using unbound controls then yes you'd have to use an append query but if you use a form/subform arrangement your child record (subform) can inherit the unique key from the parent record (main form) without the need for additional data entry or an append query.

[phineas629]

thanks for the help

[phineas629]

I was hoping you could look at my relationship report. I took your advice. I've also decided to start over from scratch and use autonumber for my primary keys. Does this seem plausible?

Here are my issues so far:
There are many clients separated into 4 insurance companies.

Sometimes a client can jump between insurance companies.

I will add another table for volunteer training info.


Any help will be grateful. I am stuck and don't want to start over from scratch once more. Thanks!

[rpeare]

Your original post was about being able to calculate an amount of time volunteer spent with various clients over a specific time period. Stingaway gave you a suggestion to combine your Volunteers and Clients which you have, but it still doesn't answer the basic question from my original post. The way you have it set up an invoice can only be comprised of a single visit by a single volunteer on a single day. If that is your intent then your setup is correct. If, however, you want to be able to produce an invoice that can potentially have multiple line items (either for a specific volunteer showing who they visited over a span of two weeks, or for a specific client to see which volunteers visited them over a two week period) then your tables are still not set up correctly. On your invoice table you'd want to have:

Invoice_PK (I assume this is an autonumber)
Invoice_Number (internally generated invoice number)
Bill_Date
Notes (Maybe depending if you have invoice specific notes or whether this is a line item note)

you'd want a subsidiary table for the actual billing detail

Invoice_PK (foreign key to your invoice table)
InvoiceDetail_PK (autonumber is fine)
Client_FK
Volunteer_FK
Units_Used (if you store the start time and end time you can calculate this if reporting start and end times is important on the invoice)
UnitRate (if you store the rate you can just multiply the units by the rate to get the total charge and it will give you the ability to create a more detailed invoice)
Notes (maybe, again if the notes are visit specific you'd want the notes here not the invoice table)
Auth_FK (if you're storing the authorization FK you don't really need the client FK because you can backtrack the authorization FK all the way to the client table)

[phineas629]

THANKS AGAIN,

My goal is to have multiple line items as you wrote. Is querying better or will your table strategy suffice?

Last questions, then I'm on my own. You wrote about me including a "subisidiary table for the actual billing detail" in the previous post. What kind of relationship should I set that table to have with my invoive table as well as the other tables? I've set up my relationships chart with your advice. I have a one on one relationship for my invoice and invoice detail. Did I do my relationships chart correctly?

Thank you Rpeare (Hopefully I can move on to forms and queries soon)

[rpeare]

I said this in my last post.

1. Your Invoice_Detail table needs to have it's on unique key field (autonumber is fine). Every table should have a unique key field to make each record easily identifiable.
2. You don't need the Client_FK in your Invoice_Detail table, you can get the client ID by backtracking from the authorization number but that's really up to you

finally, the relationship between Invoice and Invoice_Detail should be one to many, you will (or can) have many detail lines for each invoice.

[phineas629]

Thanks Rpeare