Hey guys,
I'm having a bit of a brain fart and need some help with a database question:
I'm using Access 2007 and I have a table with Patient ID's that share relationships with other tables. I need to be able to offer the user the ability to change the Patient ID from within a form.
Any ideas what the easiest way to do that would be?
Thanks!
I'd need your Access File to see how the tables are set up before I can troubleshoot the easiest way to do this.
However, since you said "patient" I figure this must be medically related and you can't send it to me because of HIPPA Laws.
You should just be able to change in the initial input form.......
or
Is that field locked or a auto-number?
You also need to worry about linked tables using the patient number as a key. If you update the number, then the linked data in other tables would not link due to mismatch in the key.
This is where I'm having the brain fart today.
I have a form for adding a new patient - that data includes a PTID and a screening date along with various ethnic information data.
Once that data is entered it goes (obviously) into a table that drives all info related via other tables (1-to-1 cascading). I need to offer an "Edit PTID" from a form but (here's the brain fart), how can I have a user edit the PTID without doing it from within the table itself?
Quote from: ryandalling on May 04, 2010, 07:35:56 AM
You also need to worry about linked tables using the patient number as a key. If you update the number, then the linked data in other tables would not link due to mismatch in the key.
Since the data cascades would that still mean that the link would mismatch?
Crap - some days this comes so easy, others (like today) I'm struggling with retarded issue.
**Edit - so I tested changing the PTID in the source table directly and the ID change did cascade down through all the linked tables. [thumbsup]
Scratch that part off at least as an issue.
If you have one main table that drives all your sub-tables / forms, then yeah, you'd only be able to change it in the table at the top of the hierarchy.
Right, that's what I want to do but not from within the table - from within a form.
(tough day today for MD)
[bang]
I am not sure that is possible.......
If there's one thing that I've learned about building databases, it not "if" it can be done but "how".
How about 2 separate IDs - one is your key between tables, one is the externally viewed one that can be modified?
Quote from: somegirl on May 04, 2010, 05:44:47 PM
How about 2 separate IDs - one is your key between tables, one is the externally viewed one that can be modified?
That's a pretty good idea.........
Quote from: somegirl on May 04, 2010, 05:44:47 PM
How about 2 separate IDs - one is your key between tables, one is the externally viewed one that can be modified?
That's not a bad idea - however, the slew of tables already in relationships are working from the PTID as the key. So adding a second ID would only muck things up.
I'm still working on it and having a better day today as far as conceptualizing the form.
Access is probably one of the worst programs ever designed.
It is confusing and just plain sucks.
Quote from: cyrus buelton on May 05, 2010, 06:36:03 AM
Access is probably one of the worst programs ever designed.
It is confusing and just plain sucks.
I wouldn't go that far. I actually really like it and have seen some amazing work done with it. You just have to be a little "creative" sometimes!
I've never used Access, my DB experience is all from Oracle or MS SQL.
SQL helps a lot and it can be used also be used in Access.
Eureka!! I figured it out!
Ok, so here's what I did: I created a form for "Edit ID's", created a query to pull a list of all the ID's listed in the main table, then inserted a subform into the "Edit ID" form that pulls the data from the query into the subform, then I created a close/save function, and voila! It works! ID's can be changed from within the subform! ;D
Thanks everyone for the suggestions! I was really having a hard time with this yesterday! [thumbsup]
Quote from: somegirl on May 05, 2010, 07:11:17 AM
I've never used Access, my DB experience is all from Oracle or MS SQL.
Access is for companies that can't afford Oracle, SAP, or any other ERP system out there.
They pay consultants a god damn fortune to build complex systems to run all their accounting, receivables, etc etc etc.
It always make the beast with two backss up and the money is wasted.
my company spent north of 8 digits on Oracle and it still make the beast with two backsing sucks.
Quote from: cyrus buelton on May 05, 2010, 08:52:32 AM
Access is for companies that can't afford Oracle, SAP, or any other ERP system out there.
You make Access sound like it's a bottom of the barrel program though. My department runs a lot of databases and though Access does have it's down sides, it's also a very powerful tool. What I have learned is that people who have a hard time with Access are the same ones that can't understand that it's not "if" it can be done but rather "how" it can be done.
I've been impressed by my boss's work and have learned to do much of the db design and construction on my own now. I use SQL with Access because it often works better that way, but still - I've not reached an impass yet - only challenges.
Quote from: Monster Dave on May 05, 2010, 08:58:53 AMYou make Access sound like it's a bottom of the barrel program though.
No, no, I'm sure it's a step above Filemaker Pro. [cheeky]
(to be fair, I haven't used FMP in about 10 years)
Quote from: somegirl on May 05, 2010, 09:21:06 AM
No, no, I'm sure it's a step above Filemaker Pro. [cheeky]
(to be fair, I haven't used FMP in about 10 years)
LOL - it's funny to think of where things were just 10 years ago...
Oh wait I started a thread about old stuff: http://ducatimonsterforum.org/index.php?topic=37685.0 (http://ducatimonsterforum.org/index.php?topic=37685.0)
Quote from: Monster Dave on May 05, 2010, 08:58:53 AM
You make Access sound like it's a bottom of the barrel program though. My department runs a lot of databases and though Access does have it's down sides, it's also a very powerful tool. What I have learned is that people who have a hard time with Access are the same ones that can't understand that it's not "if" it can be done but rather "how" it can be done.
I've been impressed by my boss's work and have learned to do much of the db design and construction on my own now. I use SQL with Access because it often works better that way, but still - I've not reached an impass yet - only challenges.
It is a bottom of the barrel program depending on how some use it.
I used to work for a commercial real estate company that used Access for everything. It was a make the beast with two backsing trainwreck. They definitely should have purchased a program to meet their needs, not just use someone that new Access to create something they didn't want to invest in. Then they wondered why certain data couldn't be extracted.
I worked two summers for this large midwest commercial real estate company maintaining and fixing that make the beast with two backsing piece of shit file (can't you tell how much I like it?)
Access is viable for certain use, but when you get into complex databases..............use a real program.
Note: I despise Oracle. It is the most backasswards operating program or our BA's don't know what they are doing.
We use Access for several things, but mostly for tracking small metrics for certain departments, etc.
It just makes printing reports a lot cleaner through exporting to crystal reports, etc.
But then again, I love excel, pivot tables, and v-look up.
Quote from: cyrus buelton on May 05, 2010, 09:53:50 AM
Note: I despise Oracle. It is the most backasswards operating program or our BA's don't know what they are doing.
I think it's the latter...you need better DBA's.
Quote from: somegirl on May 05, 2010, 10:02:49 AM
I think it's the latter...you need better DBA's.
That's what I'd say too!!
On the flip side, at least there are options!
Quote from: somegirl on May 05, 2010, 10:02:49 AM
I think it's the latter...you need better more DBA's.
Fixed it for you.
Oracle is a screwy program.
It sometimes does some very odd odd things that even Oracle can't explain.
It went down once for 3 days (yes, that halted a billion dollar in sales per year company from doing anything) and Oracle's programmers in India couldn't figure out the problem. Finally, someone over there after 72 hours got it working again.
Basically they did a system restore from the previous backup, so an entire days worth of work was erased since they couldn't fix the problem.
Quote from: Monster Dave on May 05, 2010, 10:09:22 AM
That's what I'd say too!!
On the flip side, at least there are options!
Only other option is SAP for a large scale business.
Of course you can go old school and use QAD, if anyone has ever heard of that...........
It was big in automotive mfg companies.