Question

Locked

Database logistics help

By jim ·
Hello,
I have a database in which I track production units at an agricultural farm.
I am having trouble to create a set of related tables that have random relationships.
Here's the setup
I have a table called transfergroup
PK = TGID
this table gives rise to several "types" of transfer which live in a separate table and get a unique id by TGID+"type"
My problem comes where i used TGID+"type" to graft onto a new TGID+"type"2 so in the cyclical relationship I have trouble referring to the differences between the starting TGID and the resulting TGID any help to be offered today?

This conversation is currently closed to new comments.

9 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Answers

Collapse -

Can you explain that a little?

by LocoLobo In reply to Database logistics help

Are you tracking lot#s? Something like lot143 oranges gives you lot143-27 OJ (raw) gives you lot143-27A OJ (ready for shipping)?

Sorry, I'm a little slow. Could you maybe provide a simple example?

Collapse -

OK

by jim In reply to Can you explain that a li ...

Here goes
I am creating genetic replicates, clones by transferring a bit of tissue from one plate to the next, from that same plate i use a section of tissue to propegate a large volume of vegetative growth.
So each plate serves as the seed for production units which are the large volume AND the seed for another plate which will in turn seed out more production units AND the next plate. I will continue this process for as long as my mother culture (plate to plate transfered growth) is viable.

I have several substrates from which i will transfer and to which i will transfer so I need to be able to enter that a plate was created with media A, with clone C, from tube A, that had media B. And I have about five different types of media, 40 something clone strains,and five types of tubes. All of which changes from time to time.

I will be happy to expand further or give more examples if that doesnt do it for ya.

Collapse -

I think I see

by LocoLobo In reply to OK

but I don't have an answer. Would it be possible to add a sequence number to your second table? Then you can track TGID, type, seq_no and make the trio be the PK for that table. Just a thought.

Collapse -

Not sure I'm getting what you want

by Tony Hopkinson In reply to OK

If I'm not a couple of rows of table and explanation of the links you want, may help.

Based on what I gather though.

My thought is

Plates
PlateID Media Tube Clone ParentPlate
1 A A C Null
2 B A D 1

You can them link Media, Tube and Clone to tables of type and description
PlateId can simply be auto generated or the physical ID, if it's unique
Parent plate is null for a new line and the PlateID of the plate where the 'starter culture' came from

It's called a self referencing key, some DBMS might hiccup over enforcing it with a foreign key clause.

Production I'd simple put in another table and link back to the plate id it came from.

HTHs

Collapse -

Thanks

by LocoLobo In reply to Not sure I'm getting what ...

In my lab we have a similar problem. We track our analytical standards from the neats down to each dilution we make and need to be able to show our calculations through the process. Right now we are doing it the old fashion way. (Big Chief pad and pens)

Collapse -

I think I see

by jim In reply to Not sure I'm getting what ...

I will give this a shot, unfortunately I dont have time to work with those tables until later this week. (building our database is sort of side project to my real job) I hope you'll be around when i do get to them.

Collapse -

OK I am trying it but...

by jim In reply to Not sure I'm getting what ...

How do i build a lookup for the table i am operating in?
Run a query on the unique fields and then lookup that?
I think i may have just answered my own question.
But is that the normal course of action when using a self referencing key?

Collapse -

That is NOT the answer.

by jim In reply to OK I am trying it but...

When I try to run a lookup on the query that is running on the table i am putting the lookup in, I get an error. " No valid fields can be found,... You may have selected a query that uses the table your adding the lookup column to. try using another source"

Collapse -

I got the table designed, now...

by jim In reply to Database logistics help

I have the form that does the lookup via a query, now I need to design my output. I could certainly use a hand at this part because i haven't seen it done before.
I need to take 4 different parts of each record to make a unique code. That doesnt sound difficult until i introduce the part where i have to expand out one of the fields...
One of the fields that will be part of the code is "quantity" for which i input a total quantity of 'whatevers'. I need the code to be generated for every whole number integer from 1 to n where n="quantity".
Example
TransID|VialType|MediaCode|Date|Quantity
1 | A | 01 | 030407 | 4

The generated Codes:
Date-VialType-MediaCode-IntegerNumber
030407-A-01-1
030407-A-01-2
030407-A-01-3
030407-A-01-4

Please tell me there is an easy solution to this one.

Back to Software Forum
9 total posts (Page 1 of 1)  

Related Discussions

Related Forums