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. 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;