query design issue

G

Guest

Hopefully, someone can help me with the design of the way a table looks from
a query.

Lets say I have a table that has 10 rows of member and providerinformation
in it. However, there are ONLY 5 doctors. Some of the doctors have more
than 1 patient, so they have more lines. IE -
PROVIDER MEMBER
dr. jones mary
dr. jones james
dr. jones sam
dr. adams eddie
dr. jay amy
dr. jay sally
dr. peace jake
dr. steph kenny

What I need to do is produce a report with only 5 total lines (equal to the
amount of doctors) with colums for each member.....IE
PROVIDER MEMBER1 MEMBER2 MEMBER3
jones mary james sam
adams eddie (blank) (blank)
jay amy sally (blank)
peace jake (blank) (blank)
steph kenny (blank) (blank)


Can anyone help me write a query for this procedure.....I must have the
columns as each member must stay int he same line as the provider they use.


I really appreciate it!!!!
 
D

Duane Hookom

Create a main report of just each unique Provider (no members). Then create
a multi-column subreport that is based on the Provider and Member records.
Set the subreport to display across then down. Add the subreport to the
detail section of the main report and set the Link Master/Child properties
to Provider.
 
G

Guest

I appreciate the help. I actually am not sure this will help. I am doing
this in a table, not a report. I have to export out to excel once it is
completed for another department.
 
D

Duane Hookom

I thought you were asking for a report when you stated "What I need to do is
produce a report".

You didn't provide enough information to create your query as displayed. You
didn't tell us why Mary is Member1 and not Member2 or Member3.

Would you be satisfied with a comma delimited list of members or do you need
them in separate columns?
 
G

Guest

Tables are not meant to do what you want to do - reports and queries are for
that. You need to normalize your data: One table containing unique doctors,
another containing unique patients. If a patient can have only one doctor,
that is a one-to-many relationship, and the doctor ID should be recorded in
each patient's record. If a patient can have multiple doctors, then that is a
many-to-many relationship which needs a linking table that carries patient
ID's and doctor ID's. Then a query can provide the output you need: a line
for each doctor-patient pair. You can export the output of a query to an
Excel spreadsheet. The type of query you need for your output is a crosstab
query.
 
G

Guest

Sorry about the confusion with the word "report". I did mean table. There
is no reason Mary is member #1...I just put it that way. Some of the
doctors will have more columns than others filled in. The max number of
columns will be based on the most members belonging to whichever doctor....If
one doc has 10 members, there will be 10 columns (member1 member2 member3,
etc.)with people in them....in that same tabe, a doc with one member may only
have 1 column filled in (1 member) and 9 blank ones....no rhyme or reason who
goes first....

This table will eventually be exported out into excel or text but I have to
have ever member belonging to a doctor on that docs 1 line in hte
table.....any other ideas and I will be very open to any....This may not can
be done.....
 
D

Duane Hookom

Access queries don't accept "no rhyme or reason who goes first" as valid.
You must provide a column that can be used to create "rhyme or reason". You
can use any unique field to create your "rhyme or reason".

If you want columns in alpha order by Member, you would create a crosstab
with:
Row Heading: Provider
Column Heading: "Member" & DCount("Member","[No Tablename
Provided]","[Provider] = '" & [Provider] & "' AND [Member]<='" & [Member] &
"'")
Value: First of Member
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top