Showing multiple fields from single records in a query

[Bluenose]

Hi. I am a newbie to Access. I feel I probably should know the answer to my
question but if I did, I have forgotten.

I have a query pulling information from a table, "Requests." This table
includes (amongst others) 15 fields: Ab1 - Ab15 populated from a dropdown
menu on a requisition form which looks-up records from another table,
"Antibodies" and inserts the data from a field named "Antibody." There are
two other fields in the Antibodies tables: "Pretreatment" and "Control."

I have constructed a report, based on the query, which displays (amongst
other things) Ab1, Ab2,...Ab15.

My problem is that I also want to show, alongside Ab1, Ab2, etc., its
corresponding "Control" data according to the records on the Antibodies table.

Can you guys help me out?

Many thanks

 

[Jerry Whittle]

You've committed 'spreadsheet'. What works in a spreadsheet is seldom the
best in a database. You should only have one field called something like
Antibody in your table and not the Ab1-Ab15 fields.

I highly recommend getting some relational database training or reading
"Database Design for Mere Mortals" by Hernandez before proceeding any further
on this database.

 

[Bluenose]

Oh dear. Don't really have the time for that i'm afraid. Any work arounds?

 

[vbasean]

LMBO "You've committed 'spreadsheet'"

You've committed 'spreadsheet'. What works in a spreadsheet is seldom the
best in a database. You should only have one field called something like
Antibody in your table and not the Ab1-Ab15 fields.

I highly recommend getting some relational database training or reading
"Database Design for Mere Mortals" by Hernandez before proceeding any further
on this database.

 

[Bluenose]

Yes, yes it's funny. But not helping! lol

LMBO "You've committed 'spreadsheet'"

 

[vbasean]

Bluenose,

We've all committed the 'unforgivable' at one time or another. Your problem
is complictated by your data not being "normalized"

the short definition of "normalized" is that every item should be in its own
category.

so...

in this case Request is containing two categories:
1) Request
2) Request Antibodies
so the ideal thing to do is split this information into TWO tables
whatever the primary key is for "Requests" should be a field in
RequestAntibodies
sample RequestAntibodies table could be:
RequestID - relates to the Request table primary key
Antibody
Pretreatment
Control

IF you don't want more than one type of antibody per Request
example that you don't want:
AntibodyName1
AntibodyName1
Where the name of the antibody is duplicated per request

then keep the table as such
if YOU DO want it where a request can have the same Antibody name more than
once per request then add a field
RequestAntibodiesID - Autonumber - primary key


create a relationship between the two tables on the RequestID field.

Create a subform to list the Request's Antibodies, then your set

 

[Klatuu]

vbasean may be a bit rude, but Jerry's comment is correct.

In reality, the work around may be more work that backing up a step and
doing it right. Another old quote:
Never enough time to do it right, but pleanty of time to fix it.

If you could provide a bit more info on the table schemas and how the two
table relate, perhaps we can get your through it, but still, get the book.

 

[Bluenose]

Wow, that'll give me something to chew on for the next few days. The
girlfriend won't be happy! Thanks sean.

 

[Lakeisha]

Hi. I am a newbie to Access. I feel I probably should know the answer to
my
question but if I did, I have forgotten.

I have a query pulling information from a table, "Requests." This table
includes (amongst others) 15 fields: Ab1 - Ab15 populated from a dropdown
menu on a requisition form which looks-up records from another table,
"Antibodies" and inserts the data from a field named "Antibody." There are
two other fields in the Antibodies tables: "Pretreatment" and "Control."

I have constructed a report, based on the query, which displays (amongst
other things) Ab1, Ab2,...Ab15.

My problem is that I also want to show, alongside Ab1, Ab2, etc., its
corresponding "Control" data according to the records on the Antibodies
table.

Can you guys help me out?

Many thanks

 

[Clif McIrvin]

Wow, that'll give me something to chew on for the next few days. The
girlfriend won't be happy! Thanks sean.

Yeah ... this stuff can be time-consuming, and of course, it's
production data and the customer can't wait .....


I have a half-baked (in-house) application that is both 'live' and not
fully defined. While 'waiting' for the powers that be to decide what it
is that they really want I am left working with a kludge ...

I've got data in an Excel spreadsheet that has 5 sets of data in each
row (to your 15).

I have created a set of 5 select queries and a union query to make this
data appear somewhat normalized. It works -- sort of.

In the long run, you will definately be better off normalizing your
table structure.

 

[vbasean]

I would like to add on to this by correcting a couple things I said:

the RequestAntibodies table should have a two field key
RequestID
Antibody

by selecting both of these fields (click both while holding the control
button) and selecting the primary key button (the one that looks like a key
[man I wish I could post screen shots!!]) you create a two field key

this forces the database to only accept one Antibody of a kind per request.
if this is not intended then go with a new field for your primary key to
this table
maybe an autonumber
RequestAntibodyID - autonumber - only field marked as primary key


once this is complete you can create a form for Requests
then create a sub form for RequestAntibodies
on the sub form RequestAntibodies you can have a text boxes updated on the
After_Update event of the combo that enters the correct info

Let me know when you get this far and I (or someone faster than me, there's
pleanty of them) will walk you through the combo/text box update scenario.

 

[Bluenose]

Thanks for the advice, sean. I having managed to get round to putting it into
practise yet. I have displayed my modified email in my profile so you can
drop me an email with the screenshots if possible and I can reply to it when
I am ready for help on the combo/text box update advice.

Thanks

I would like to add on to this by correcting a couple things I said:

the RequestAntibodies table should have a two field key
RequestID
Antibody

by selecting both of these fields (click both while holding the control
button) and selecting the primary key button (the one that looks like a key
[man I wish I could post screen shots!!]) you create a two field key

this forces the database to only accept one Antibody of a kind per request.
if this is not intended then go with a new field for your primary key to
this table
maybe an autonumber
RequestAntibodyID - autonumber - only field marked as primary key


once this is complete you can create a form for Requests
then create a sub form for RequestAntibodies
on the sub form RequestAntibodies you can have a text boxes updated on the
After_Update event of the combo that enters the correct info

Let me know when you get this far and I (or someone faster than me, there's
pleanty of them) will walk you through the combo/text box update scenario.

Bluenose,

We've all committed the 'unforgivable' at one time or another. Your problem
is complictated by your data not being "normalized"

the short definition of "normalized" is that every item should be in its own
category.

so...

in this case Request is containing two categories:
1) Request
2) Request Antibodies
so the ideal thing to do is split this information into TWO tables
whatever the primary key is for "Requests" should be a field in
RequestAntibodies
sample RequestAntibodies table could be:
RequestID - relates to the Request table primary key
Antibody
Pretreatment
Control

IF you don't want more than one type of antibody per Request
example that you don't want:
AntibodyName1
AntibodyName1
Where the name of the antibody is duplicated per request

then keep the table as such
if YOU DO want it where a request can have the same Antibody name more than
once per request then add a field
RequestAntibodiesID - Autonumber - primary key


create a relationship between the two tables on the RequestID field.

Create a subform to list the Request's Antibodies, then your set