Pre-defined fields

[Dave]

Hi All,

I have built a database to track the storage of specimens in liquid
nitrogen tanks. They are frozen in little vials in trays (field: Tray).

Each tray has 56 spots (field: spot) for the vials. And, of course, I
have fields for patient name etc for each spot that has a vial. I would

like to create a report, grouped by tray (this is no problem) that
shows all 56 'spots' whether or not there is a patient recorded in that

spot. For example, spots 1, 2 and 3 might show "Joe Smith" 6, 7, and 8
might hold "Joe Blow" but I would like to see spots 4 and 5 print out
in the report empty. But I think I might have set the tables up wrong!
I have one table that
includes Tray and Spot. As I'm registering a sample I choose a spot (1
through 56 because I know thats all that are available). Therefore,
Access doesn't know that I have 56 spots! That might be my problem. How

can I pre-define that there are 56 available spaces per tray?


These are my first posts to this group and I'm mighty impressed by the
thoughtful and quick responses. Thanks.




Dave B


Reply »

 

[Rob Parker]

Hi Dave,

To do this, you need a way to introduce all tray spots into your recordset.
There are probably other ways, but a simple one is to create a separate
table (tblTraySpots) containing all the tray spots you want - it only needs
a single field, and there will be 56 records.

Include this new table in the query that your report is based on, with a
left (or right - depending on your actual query design) join to the spot
field in your existing table - in the query design grid, right click the
link, then select "Show all records from tblTraySpots and ..." in the Join
Properties tab. If you have other joins in your existing query, and you get
an "ambiguous join" message when you try to run the query, you might need to
re-arrange things to get all joins flowing in the same direction.

Use the TraySpot field from this table in your report to ensure that every
trayspot appears.

HTH,

Rob

 

[Rob Parker]

Precisely! Thanks for the catch, Duane.

Rob

Great reply. It is assumed that "it only needs a single field, and there
will
be 56 records" means to add records with values 1 through 56.

 

[Al Campagna]

Dave,
Probably what's more important here is whether you need historical data about specimen
placement, or just the ability to "snapshot" report the current contents of any tray
number.
Do you need to know that Joe Blow had specimens in the tray 11 two months ago, or just
"what's in there right now?"

If Snapshot only, I'd make Trays the ONE table (on the Main Form), and Spots the MANY
table (in a continuous SubForm)... Parent/Child related via a key field TrayNo.
You might also want to plan ahead by setting up a TankNo in case you might have
multiple tanks in the future.
(TankNo 3/TrayNo 4/SpotNo 16)

Populating the subform...
1. "The pure grunt method"... Create a MainForm record for all your trays.
Manually enter 1 thru 56 SpotNo records (with no other data) for Tray 1 in the subform.
Copy and paste those records to Tray 2's subform, Tray 3's subfrom etc... for all your
trays.
2. "A bit more elegant method"... Create a button in the subform header called
cmdPopulate. Code it so the button only enables if there are NO subform records as yet.
(DCount SpotNo records for this tray = 0). If you add trays in the future, that button
will be ready to go for them.
Dim Ctr as Integer
DoCmd.GoToRecord,, acNewRec
For Ctr = 1 to 56
SpotNo = Ctr
DoCmd.GoToRecord,,acNext
Next Ctr
3. Or... use DAO to do the essentially the same thing in table Spots against
every TrayNo in tblTrays.

--
hth
Al Campagna
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions



Hi All,

I have built a database to track the storage of specimens in liquid
nitrogen tanks. They are frozen in little vials in trays (field: Tray).

Each tray has 56 spots (field: spot) for the vials. And, of course, I
have fields for patient name etc for each spot that has a vial. I would

like to create a report, grouped by tray (this is no problem) that
shows all 56 'spots' whether or not there is a patient recorded in that

spot. For example, spots 1, 2 and 3 might show "Joe Smith" 6, 7, and 8
might hold "Joe Blow" but I would like to see spots 4 and 5 print out
in the report empty. But I think I might have set the tables up wrong!
I have one table that
includes Tray and Spot. As I'm registering a sample I choose a spot (1
through 56 because I know thats all that are available). Therefore,
Access doesn't know that I have 56 spots! That might be my problem. How

can I pre-define that there are 56 available spaces per tray?


These are my first posts to this group and I'm mighty impressed by the
thoughtful and quick responses. Thanks.




Dave B


Reply »

 

[Dave]

Great Help!!

I'm at home right now so I can't try it out, but I'm going to first
thing in the morning when I get to work. It sounds like it's just what
I need. Probably pretty simple for you guys but it's my first database
and I'm actually having fun with it. Thanks. Will report back.

Dave B

 

[Dave]

Thanks Rob

I'm sure I understand what you mean, but It's not working and I think I
know why. I failed to mention ('cause I didn't think it mattered!) that
there are several 'Canisters' each with 8 'trays'. Each of those trays
has 56 'spots'. I have one table with fields for canister, tray and
spot. Your advice worked to a point but all 56 spots do indeed show but
were spread out over all the different trays. I need each tray to show
all 56 spots on the report. Do I have any other simple recourse? Thanks
Again.

Dave B

 

[Dave]

Thanks Rob

I'm sure I understand what you mean, but It's not working and I think I
know why. I failed to mention ('cause I didn't think it mattered!) that
there are several 'Canisters' each with 8 'trays'. Each of those trays
has 56 'spots'. I have one table with fields for canister, tray and
spot. Your advice worked to a point but all 56 spots do indeed show but
were spread out over all the different trays. I need each tray to show
all 56 spots on the report. Do I have any other simple recourse? Thanks
Again.

Dave B

 

[Rob Parker]

That's the start. But it gets a little trickier, because if you simply do
that, and add that table to the query with a left join (so that you have
both the "completeness" tables joined to the original table/query), you'll
get an "ambiguous joins" error message.

You need to do it in two steps. And, if you also want to force each
Canister to appear in your report, you'll need a table of canisters.

So, set up the three "completeness" tables; let's call them tblCanisters
(containing a single record for each possible canister), tblCanisterTrays
(containing a single record for each possible tray), and tblTraySpots
(containing a single record for each possible spot). Then create a query,
let's call it qrySampleLocations; drag each of the three tables into it, and
add each field to the query. Do not create any joins between any of the
tables. When you run this query, it will return the cartesian product of
each canister, tray and spot - if you have 4 canisters, each with 8 trays,
each with 56 spots, it will return 4 x 8 x 56 (1792) records.

Then create the query for your report, using the table/query that contains
the sample data; this must have fields for canister, tray and spot, together
with any other fields you need. I'll assume that this data is in tblSamples
or the next bit. Create a new query based on qrySampleLocations and
tblSamples. Join the Canister field from qrySampleLocations to the Canister
field in tblSamples with a left join; join the CanisterTray field from
qrySampleLocations to the Tray field in tblSamples with a left join; and
join the TraySpot field from qrySampleLocations to the Tray field in
tblSamples with a left join. Then add the three location fields from
qrySampleLocations to the output fields of the new query, and any other
fields you need from tblSamples. The SQL for the final query will be
similar to:

SELECT qrySampleLocations.Canister, qrySampleLocations.CanisterTray,
qrySampleLocations.TraySpot, tblSamples.Description
FROM qrySampleLocations LEFT JOIN tblSamples ON
(qrySampleLocations.TraySpot = tblSamples.Spot) AND
(qrySampleLocations.CanisterTray = tblSamples.Tray) AND
(qrySampleLocations.Canister = tblSamples.Canister);

If you don't want to show all possible canisters, the solution becomes a
little trickier, since omitting tblCanisters from qrySampleLocations, and
using the Canister field from tblSamples in the final query, will give
output with null entries in the Canister field for all the vacant trays and
spots. It would probably be easier to use some VBA code in your report (I'm
assuming that the query will be used as the source for your report, and that
you are using grouping/sorting within the report) to omit any Canister group
which has no detail (eg. sample description) data. Post again if you need
help there.

HTH,

Rob

 

[Rob Parker]

A quick solution to the final point in my previous post:

Use a query which returns the canisters in use, rather than all possible
canisters, instead of tblCanisters, in qrySampleLocations. It will be of
the form:

SELECT DISTINCT Canister FROM tblSamples;

You could add a WHERE clause if necessary.

HTH,

Rob

 

[Dave]

Thank you Rob,

Your advice worked perfectly. You're a true genius. Thanks again!

Dave B

 

[Rob Parker]

You're welcome. Glad to have helped.

Rob

Thank you Rob,

Your advice worked perfectly. You're a true genius. Thanks again!

Dave B