- My Forums
- Tiger Rant
- LSU Recruiting
- SEC Rant
- Saints Talk
- Pelicans Talk
- More Sports Board
- Fantasy Sports
- Golf Board
- Soccer Board
- O-T Lounge
- Tech Board
- Home/Garden Board
- Outdoor Board
- Health/Fitness Board
- Movie/TV Board
- Book Board
- Music Board
- Political Talk
- Money Talk
- Fark Board
- Gaming Board
- Travel Board
- Food/Drink Board
- Ticket Exchange
- TD Help Board
Customize My Forums- View All Forums
- Show Left Links
- Topic Sort Options
- Trending Topics
- Recent Topics
- Active Topics
Started By
Message
re: Any Microsoft Access Experts?
Posted on 1/17/14 at 1:25 pm to Korkstand
Posted on 1/17/14 at 1:25 pm to Korkstand
I have foreign keys set up from the unique ID key in main database to the foreign keys in each of the separate competitive team tables. The problem is that the ID never transfers when I create a new record in the main database.
And I sincerely appreciate the help, even though I might sound retarded.
And I sincerely appreciate the help, even though I might sound retarded.
This post was edited on 1/17/14 at 1:26 pm
Posted on 1/17/14 at 1:25 pm to TheOcean
quote:
How do I make it xfer then? And is there an easier way to do what I'm trying to do? It's really not a complicated table, but this is the main piece I'm missing.
The point of relational databases is so you don't have to transfer or duplicate information. The foreign key is there for the purpose of linking a record in one table to a record(s) in another, and you gather the information you need via queries rather than thinking of it like you need to read a spreadsheet and see all the info on one "sheet".
Posted on 1/17/14 at 1:28 pm to TheOcean
Your design is the problem I think. See the long post aboout 2 above. You need an alumni table, a team table, a competition table, and then a "results" table to store the combinations. So my alumni Id, team Id, and competition id would be stored.So if I'm A,on team B, inn competition C the results table would store ABC in a row (separate columns). Then if I'm on team B, but in competition D it would store ABD.
Posted on 1/17/14 at 1:29 pm to TheOcean
I suggest that your either 1. study relational database theory/understand the relational model or 2. if you are not willing to invest in this learning you might be better off just using Excel.
Posted on 1/17/14 at 1:30 pm to Tortious
quote:
I think your design maybe part of the problem. If someone is on two teams would you want the data in both team tables right?
Lets start with table design. In the simplest(it's normalized but simple) form what I would do is have four tables.
PKs are bold, FKs are Italics
Alumni
Al_ID, Name, Info Fields....
Alumni_team_xref
Al_ID, Team_ID
Team_Master
Team_ID, Desc_Fields...
Competitions
Comp_ID, Team_Id, Comp_date, Detail fields...
This post was edited on 1/17/14 at 1:41 pm
Posted on 1/17/14 at 1:31 pm to TheOcean
quote:
The problem is that the ID never transfers when I create a new record in the main database.
you're going to have to write a query that does this...nothing is going to just transfer over. You essentially need like a stored procedure that updates everything on a submit button or something (if I'm reading it correctly)
Posted on 1/17/14 at 1:32 pm to Tortious
That makes perfect sense, I'm just not sure how to execute it. Do you have a junk email I could send you the db when I finish it (doing it the way you just described)?
Posted on 1/17/14 at 1:32 pm to mikelbr
So it's the same design tortious is recommending?
Posted on 1/17/14 at 1:33 pm to TheOcean
quote:Well, the last time I touched Access was about a decade ago, so I can't be of much help there. All I can say is that it sounds like you either need to verify that an actual relationship is set up between the tables (as opposed to just having a field where the foreign key goes), or you need to check the form you have made (that's how Access works, right? You create forms within the program? )
I have foreign keys set up from the unique ID key in main database to the foreign keys in each of the separate competitive team tables. The problem is that the ID never transfers when I create a new record in the main database.
quote:No problem. And don't feel bad, I probably sound retarded to a real database guy.
And I sincerely appreciate the help, even though I might sound retarded.
Posted on 1/17/14 at 1:33 pm to Doldil
quote:
you're going to have to write a query that does this...nothing is going to just transfer over. You essentially need like a stored procedure that updates everything on a submit button or something (if I'm reading it correctly)
Yes when a new alumni enters his/her info and selects a team, the submit button needs to then bring up a form or make visible a panel that allows him/her to enter in multiple competitions,details for the selected team.
Then a confirm button will INSERT records(via a query that uses form values) into the appropriate tables.
eta: yea get a book.
This post was edited on 1/17/14 at 1:35 pm
Posted on 1/17/14 at 1:34 pm to TheOcean
quote:
So it's the same design tortious is recommending?
yea. I was just making it a little easy to see so if he has design view of tables pulled up.
It's fun to see DB nerds in here with me. It's only thing I know more about than drinkin/frickin/rugby.
Posted on 1/17/14 at 1:34 pm to mikelbr
I have a book and a shite ton of videos on database design. Unfortunately it's not very helpful, as you can tell.
Posted on 1/17/14 at 1:35 pm to mikelbr
Mind if I send you a copy of the db after I make the changes you and tortious suggested? You could probably pinpoint the issue in less than a minute.
Posted on 1/17/14 at 1:35 pm to TheOcean
quote:
I have a book and a shite ton of videos on database design. Unfortunately it's not very helpful, as you can tell.
Well start with the design that we've given you. The form should come after DB design. I know that's a difficult concept unless you're a trained IT nerd. We have engineers here who are brilliant and do it backwards too.
Posted on 1/17/14 at 1:36 pm to TheOcean
Yes miklb is saying the same thing.Then you would use a form where you have the name, team, and competition. You select the appropriate name,team ands competition and submit that to the database. You would have to enter somweone twice of was on more than one team. There are ways to do it once but honestly may be a bit over your head.
Posted on 1/17/14 at 1:37 pm to TheOcean
quote:
Mind if I send you a copy of the db after I make the changes you and tortious suggested? You could probably pinpoint the issue in less than a minute.
Probably not. I'll spend rest of day looking at your shite and doing it for you. for free.
I ain't 13 yrs in the field for nothing.
I gave you a decent framework. Make that shite. then make a form with wizard if you need to.
Posted on 1/17/14 at 1:38 pm to TheOcean
quote:
I have a book and a shite ton of videos on database design. Unfortunately it's not very helpful, as you can tell.
This is a generic 'student athlete' database schema. This site has tons of example schemas for many situations.
Posted on 1/17/14 at 1:43 pm to mikelbr
I would have hooked you up with a shipment of some fine CCB brews.
Anyways, bookmark this shite and check it in a couple of days, I'm sure I will have something fricked up
Anyways, bookmark this shite and check it in a couple of days, I'm sure I will have something fricked up
Posted on 1/17/14 at 1:43 pm to Korkstand
Anddddd this is why I'm out this thread. I just realized my xref table had AL_ID as PK and Team_ID as FK.
Why didn't anyone catch that? DUHHHHH
Both fields are the PK aka candidate key in that table. it's a many to many relationship(remember cardinality, yall?) with alumni to teams. DOH! on my part.
But I fixed it so both fields are bolded.
I'm out before I take on this shite and don't spend rest of day in Weekend plans thread.
Why didn't anyone catch that? DUHHHHH
Both fields are the PK aka candidate key in that table. it's a many to many relationship(remember cardinality, yall?) with alumni to teams. DOH! on my part.
But I fixed it so both fields are bolded.
I'm out before I take on this shite and don't spend rest of day in Weekend plans thread.
This post was edited on 1/17/14 at 1:45 pm
Posted on 1/17/14 at 1:46 pm to TheOcean
Post an email Ocean and I'll send you a sample that will work or I can reply to you and then you can send me the db and I can fix it.I'll do it later today if brews may be involved.
Popular
Back to top
Follow TigerDroppings for LSU Football News