Database Design

[Trek9635]

Hello Everyone. I was given this exercise and not entirely sure how to make it happen in access. I would say I am a beginner/intermediate user as far as importing excel tables and creating queries for export into excel, and creating reports out of pre existing data warehouse databases. I have not really done much as far as creating databases from scratch. I created a query to sum "Room Time" charges and a separate query from the table to sum the "Drug" charges and then a third query to IIf statement. Pretty sure there are far better ways to accomplish this. I was wondering if anyone can steer me in the right direction. The description is attached for review.
Views: 6 Size: 25.0 KB">Charges Exercise.doc

Here is the sample table.

Patient	Item Description	Charge
DAVIS	ROOM TIME	-369.00
DAVIS	ROOM TIME	369.00
DAVIS	DRUG	1,356.00
JACKSON	ROOM TIME	369.00
JACKSON	DRUG	1,356.00
RYAN	DRUG	1,356.00
SMITH	ROOM TIME	369.00

[June7]

Not going to do your homework for you. Do your best to answer the questions and present here for critique. I will offer this - consider tables, at a minimum:

tblPatients

tblPatientDrugs

tblPatientRoom

[Trek9635]

Ok thanks.

[orange]

Ditto on what June said. But I will point you to a tutorial that you should work through. You will learn.

Good luck.

[June7]

Well, I think maybe my answer was a little abrupt. Since you did attempt queries (would have been nice to post them) I will expand my comments.

The assignment does say 'build a database' and your thread topic is 'Database Design' - to me that means a relational structure.

However, using the data of the given sample table, your 3 queries is a reasonable approach but 1 SQL statement can do it all:

SELECT Patient, Sum(IIf(ItemDescription="Drug",Charge,0)) AS SumDrugs, Sum(IIf(ItemDescription="Room Time",Charge,0)) AS SumRoom, IIf(Sum(IIf([ItemDescription]="Drug",[Charge],0))=0,"No Drug",IIf(Sum(IIf([ItemDescription]="Room Time",[Charge],0))=0,"No Room Time","Has Both")) AS Test
FROM [tablename]
GROUP BY Patient;