Designing my database

[ldypurple]

Hello all



I have studied databases over the years, but am trying to actually build this one from scratch. I hope to be able to set this up by creating the tables, forms, etc. I know i will run into trouble with relationships as I get stuck there everytime. I have tried using sample databases, but to no avail.

Here is the logic of my database and I am attaching a picture of the tables. I did not create any forms as I need to be sure the tables are correct. As i look at this picture I think, surely I am on the right track, but then something happens and I feel lost again. I am not sure of my relationships. I get stuck here time and time again. What am I missing? Here goes . . .

• I work at a hospital and must track all of the patient meals that are delivered. A group of people called the Catering Associates (CAs) deliver the meals. They submit to me a paper report that shows what meals are delivered. The meals are delivered three times a day, Breakfast, Lunch and Dinner. The meals can be regular meals, tube feedings or supplements.
• Exhibit 1 - is the spreadsheet the CAs use to report what is delivered.
• Exnibit 2 is the spreadsheet I use to combine on three meals. These meals are delivered every day and at the end of the month, I must submit a report showing several breakdowns:
  
  • How many tubefeedings
  • How many supplements
  • How many regular meals
  • To which department or floor were these items delivered
  • How many NOW trays did we serve for the day - NOW trays are trays that are called for when a person is admitted to the hospital late after a meal has been served or it can be a tray that was delivered because a patient has returned from x-ray or something and is NOW ready to eat. NOW trays can be sent to the Emergency dept if they decide to keep a patient overnight.
  • how many LTACH meals have been served - LTACH are the long term acute patients
  • how many Out Patient meals we serve - Out Patients or OPPU and OPO are patients who eat but won't spend the night
  • how many Observation meals we serve - OBS Meals are patients who have a procedure done and they are being

[orange]

Work through this tutorial. The procedure will help you with your tables and relationships.
http://www.rogersaccesslibrary.com/T...lationship.zip

Good luck

[alcapps]

where is your attempt at making your tables or am I missing something?

[alcapps]

you could ask the question about the paper report for the input of the meals delivered to see if you can get a delimited file with the data on it as well and you can automate the input part of process as well. if they are producing the paper report using a pc they might be able to output an xml file or csv file that you could use as input to your database.

[ldypurple]

Oops! I didn't realize that i didn't upload my tables or at least a picture of the tables and relationship that I tried to set up. I will get those for you now. BTW - it was hard for me to get the ladies to use the forms that i sent you. They were using just plain white print paper and writing on it. I will have to be gentle as they don't like that idea of a newbie coming in and changing things - I'll work toward that slowly.

Give me a few mins and I will upload the table and picture of the relationship. Thanks for your response.

[ldypurple]

Okay - i have saved the database from Access 2010 to Access 2003 so more peeps can take look.
I have also attached a picture of my attempt at the relationship with notes in PDF.
Feel free to be straight with me - I am here to learn. It wont' be easy, but I believe I can do it. Thanks again.

[alcapps]

yes.. looking at the relationship.. I think you are having an issue understanding table structures..

NotTraytbl..LTACHtbl and Patientmealtbl

tblMeals
MealID
MealDate
TypeMeal ...... lookup table.. (Breakfast,Lunch,Dinner, NowBreakfast,NowLunch,NowDinner,....)
DeptID
SuppID
NumberofMeals


remember if you have time sensitive data you need all pieces of information.

deptid and suppID for that meal day.

junctiontble not needed unless you want to ..

ID
SuppID
DeptID
Date

just some beginning thoughts.
It is great that your trying to understand and do your own database.. keep going..

[ldypurple]

I have narrowed things down a bit and am using the MealTbl (please forgive my naming convention with the TBL, FRM, RPT at the end) it seems to be habit). So I submit to you my database with MealTbl being my main thang with a couple of other tables to assist with the information. I created lookup for the MealType, SuppType as I must be able to report that at month end separately. The dept number is more important than the name so I set up a look up to simply choose which one. I really don't know if I need the SuppId as much as the type of supplement (plz help me with that thought).

Question - LTACH - this group is special. They are a contracting group with the hospital. We serve them but they must pay the hospital for our service. I am required to submit a report to show how many meals that get daily with a month end total. I must also report their caterings and floorstock purchases. Catering is easy. We have an online catering system and it gives a report at month end for every department. All i need to do is run a total on LTACH caterings. The Floorstock - is an online ordering process as well. Floorstock is items such as coffee, sugar, straws, coffee stirrers, cookies, blah, blah, blah, that the nurses order to keep a small stash at each station. I can run the report to get the Floorstock dollars.

I will need to add my meal report the catering and floorstock and then create a bill to submit to LTACH at month end. I am attaching a spreadsheet for what I do now. It is working okay, but I know it can be automated in Access. I started out with an LTACH table in my database, but now realize that is not necessary. LTACH is simply a mealtype. My query will extract what i need from the meals.
Let me know your thoughts.

Thanks so much for how you all are willing to lend a hand.

[alcapps]

Is LTACH a department?

with tblmeal + tblDept you can do all of what you ask for so far.

Did you need to capture the room number or patient names?

to get the numbers that you included in the spreadsheet..
create a crosstab query on tblMeal

row header is Mealdate
column header is mealtype
value is numberofmeals
where deptid = (LTACH) deptID
and mealdate between #1/1/2013# and #1/31/2013#

then use the cross table query in your report and do the sums for each type or use the total row option.

[alcapps]

for grandtotal
select sum(numberofmeals) as [Grand Total]
from tblMeal
where deptid = "LTACH"
and mealdate between #1/1/2013# and #1/31/2013#

[ldypurple]

• the contractor LTACH has been designated a department with its own number.
• no i do not need to have any information about the patient as I charge the client (hosp) for everything.
• looking at my table, it all seems to be more work than I am doing now. I will have a very large list of things to key just for day one. Please check out my attachments. I think I will have to stop, get in a corner and read all of my documents re: databases and then refresh my memory as it has been a minute since i used Access. I finally have a job that needs Access so bad and all we can do to automate and I can't get my first database together. I will not be defeated however. If you are patient with me, I will win!
• See attachments

[alcapps]

if these are spreadsheets you could create a import process instead of re inputting the data again.
go out to google and google importing data from excel into ms access. I've been doing that forever.
pseudo code
you can create a filename loop in a directory..(where you put all your spreadsheets to import)
open up each excel file
add the data to the database
close excel

now break down each piece and it is not that hard.
If all the spreadsheets are the same it makes it much easier.

if you have to key in the same things every day. make a template table with the key fields then you type in the Meal count only and leave blank the values you don't use that day. Then create an append query that uses that temp table to import the records to your database everyday with the correct date. That would be easy as well. That way you only key in the Meal count every day or add a new dept. click a button and they get added to your database and the temp table clears the MealCount for the next run. (using MealsTbl2 as an example)


Good luck..