re: Any Microsoft Access Experts?

Apparently there is an equivalent called dlookup in Access.
That may point you in the right direction

quote:

---

there is an equivalent called dlookup in Access.

---

DOnt use the dlookup

Use a query. There is a query wizard in Access

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

Do you even mysql bro?

why are you ignoring me?

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.

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.

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.

Read my second to last post (now third to last).

quote:

---

quote:

---

And are you making a separate table for each team?

---

Yup. Because each team table will have different competitions, team finishes, etc.

---

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:

---

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?

---

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.