- 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
Posted on 1/17/14 at 12:38 pm to TheOcean
Apparently there is an equivalent called dlookup in Access.
That may point you in the right direction
That may point you in the right direction
Posted on 1/17/14 at 12:39 pm to Breesus
My idea was to set up check boxes inside the main database form where you type in the main info, then check the boxes for the competitive team that person competed in, and then the unique ID + the first name + last name would automatically xfer to that competitive team table.
Posted on 1/17/14 at 12:39 pm to Schmidt
quote:
there is an equivalent called dlookup in Access.
DOnt use the dlookup
Use a query. There is a query wizard in Access
Posted on 1/17/14 at 12:40 pm to Breesus
This post was edited on 1/17/14 at 12:42 pm
Posted on 1/17/14 at 12:40 pm to TheOcean
quote:
My idea was to set up check boxes inside the main database form where you type in the main info, then check the boxes for the competitive team that person competed in, and then the unique ID + the first name + last name would automatically xfer to that competitive team table.
Use the query wizard and joining the competitive tables with the main database table only in the categories you wish to join.
Play around with that and see if it works
Posted on 1/17/14 at 12:46 pm to TheOcean
quote:
So the main database will have all of the alumni info tied to the unique ID. I then have completely separate tables for the the competitive teams. The only info I would like to go from the main database to the competitive teams is the unique ID and the first name + last name. That way queries will be easy to run.
If the first and last names are already in your main table, then you don't need to duplicate them in your team tables.
And are you making a separate table for each team? If so, you might consider making a 'teams' table that gives each team an ID (so it would just need an ID column and a name/description column), and then a table called something like 'alumni_teams' that just has two columns, one for alumni ID and one for team ID, which as someone else mentioned would just reference the foreign keys in the alumni and teams tables.
Posted on 1/17/14 at 12:51 pm to Korkstand
quote:
And are you making a separate table for each team?
Yup. Because each team table will have different competitions, team finishes, etc.
quote:
If so, you might consider making a 'teams' table that gives each team an ID (so it would just need an ID column and a name/description column), and then a table called something like 'alumni_teams' that just has two columns, one for alumni ID and one for team ID, which as someone else mentioned would just reference the foreign keys in the alumni and teams tables.
You lost me. Could you explain it to me like I'm in pre-k?
Posted on 1/17/14 at 12:56 pm to Breesus
I just watched some videos on queries and I don't think it's what I'm trying to do. I thought the point of setting up relationships was to transfer unique IDs to separate tables.
Posted on 1/17/14 at 1:10 pm to TheOcean
Presumably you are using a form to add the people by name to the other tables. Have a dropdown list with their names in it and the ID (you can do this by query). Bind the list to the ID and use column width to hide the id in the drop down. The user will be selecting the name but the form will store the id.
Posted on 1/17/14 at 1:14 pm to Tortious
Maybe I should have been more specific/clear. Here's the best way I can describe what I'm trying to do:
I have a main database with all of the alumni information and then 4/5 separate tables for different competitive teams. I have a form for the database where the user will enter all of the alumni info and then click yes/no if the alumni competed on a certain team. From there, the user will then go to the table where the alumni competed and then enter in the competition, place, etc. Thus, I need the unique ID from the alumni main database to transfer to the competitive team table when the user clicks yes/no for a certain team.
I think the answer to the question has to do with foreign keys, but for some reason, when I use a foreign key, none of the data is transferred from one table to another.
I have a main database with all of the alumni information and then 4/5 separate tables for different competitive teams. I have a form for the database where the user will enter all of the alumni info and then click yes/no if the alumni competed on a certain team. From there, the user will then go to the table where the alumni competed and then enter in the competition, place, etc. Thus, I need the unique ID from the alumni main database to transfer to the competitive team table when the user clicks yes/no for a certain team.
I think the answer to the question has to do with foreign keys, but for some reason, when I use a foreign key, none of the data is transferred from one table to another.
This post was edited on 1/17/14 at 1:15 pm
Posted on 1/17/14 at 1:17 pm to TheOcean
You need foreign keys for relational purposes but it will not transfer automatically.
Posted on 1/17/14 at 1:18 pm to Tortious
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.
Posted on 1/17/14 at 1:19 pm to TheOcean
quote:
I've started this topic ~3 times before. Need some help
I'm going to be a top 3 expert here. seriously. What you got?
eta: obviously not good at reading first. let me catch up.
This post was edited on 1/17/14 at 1:20 pm
Posted on 1/17/14 at 1:20 pm to mikelbr
Read my second to last post (now third to last).
Posted on 1/17/14 at 1:21 pm to TheOcean
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?
Posted on 1/17/14 at 1:22 pm to TheOcean
quote:That sounds like all the more reason to lump all of the teams into the same table so you aren't making queries that have to check several tables for stuff.quote:Yup. Because each team table will have different competitions, team finishes, etc.
And are you making a separate table for each team?
quote:quote:You lost me. Could you explain it to me like I'm in pre-k?
If so, you might consider making a 'teams' table that gives each team an ID (so it would just need an ID column and a name/description column), and then a table called something like 'alumni_teams' that just has two columns, one for alumni ID and one for team ID, which as someone else mentioned would just reference the foreign keys in the alumni and teams tables.
OK, but I'm no DBA by any means, but I do have some experience working with (mostly mysql) databases. And I don't know exactly what type of teams or competitions you are talking about, so all of this may not applye, but...
The basic idea is if you can describe a group of things as a certain 'type' or broadly categorize them so that they all have the same set of attributes, they should all go in one table. For example, I would make a 'teams' table that gives each team an ID, a name, a description, or whatever info a team should have. If the teams can "belong" to different types of sports or games or what have you, then I would make a 'sports' table as well.
The basic premise of relational databases is that information in one table is linked to information in another table by using "foreign keys", which is what you are trying to do by putting the alumni ID in your teams tables. This allows you to create different types of relationships: one-to-one, one-to-many / many-to-one, and many-to-many. If each alumni will only belong to one team, then that is a many-to-one since many alumni will be on one team, and looking at it from the team perspective one-to-many. If they can each belong to several teams, though, then you have a many-to-many relationship, which simply requires a third table that links IDs from each of the other two. So, no matter how many new teams are formed, you never have to create a new table. Instead, you just add a record to your teams table and you can start linking alumni to it via the third table ('alumni_teams' in my previous post).
Taking this further with your situation, you might create a 'competitions' table that gives each competition an ID, name, descriptive info, date, or whatever. And then you would have a 'teams_competitions' table with just two fields for the foreign IDs: team_id, and competition_id.
If you break things out like this, you aren't duplicating any information, and you are making it easier to run arbitrary queries later on that you may not think of now. For example, you could query the 'alumni_teams' table for a specific team ID to build a list of alumni who are on that team (the database-y way to do what you were attempting by making separate tables for each team). Or, you could query the 'teams_competitions' table to build a list of teams that competed in a particular competition, or build a list of competitions that a particular team competed in.
Posted on 1/17/14 at 1:23 pm to Tortious
quote:
If someone is on two teams would you want the data in both team tables right?
Someone could theoretically be on a max of three competitive teams and could compete in a max of 5-6 competitions.
Popular
Back to top
Follow TigerDroppings for LSU Football News