Add Field from Another Table Based on Value (1 Viewer)

robsmith

New member
Local time
Today, 12:28
Joined
Feb 17, 2020
Messages
26
Hi all,

I have two tables, BBB and ScB, and have designed a query to make a new table. In essence the new table just adds a field to BBB from ScB.

The query works ok, with one exception.

Ideally, I would like the field from ScB to be added to the new table only if the value in a field called Attendance in BBB is 1.

I'm an Access novice but assume this is possible. Can anyone point me in the right direction?

Thanks
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 20:28
Joined
May 7, 2009
Messages
19,169
advice to have uniform number of fields when creating new table.
this will save you from future, having fields on other table that are not on the other.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 07:28
Joined
Feb 28, 2001
Messages
26,996
This sounds like a structural design issue. Having two tables that differ by only one field is not only wasteful but sets you up for maintenance issues in the future. So... supposed that you make the copy of BBB plus one more field. Now, two days later you have to come back to update some data in BBB. Are you going to have to update the data in the new table as well?

You claim to be a novice, so that suggests to me that you need to understand a couple of ideas.

1. Read up on "database normalization." I wrote it that way because in a general web search, you have to use both words. There are other types of normalization in math, politics, chemistry, and maybe a couple of other subjects that slip my mind. If you do that search within this forum with the SEARCH feature in the top row next to your login name, you can drop "database" because this IS a database forum.

2. Read up on queries AFTER you learn about normalization. It is possible to use a JOIN query to put tables together temporarily so that you can use their combined information. And here's an important thing to know: Access allows you to use queries in most places that normally might need tables. Access does not "care" most of the time about the source of records. It only cares that it HAS a source. Granted, there are a few cases where a query has trouble as a .RecordSource for things that do updates. But for display purposes, queries are your friends.

How this applies to you is that if there is a relationship between two data sets, you can often JOIN them together and use them as one source. Like having your BBB table and having another table that includes this extra field. Then normally, if you update BBB, the query that joins your data sets will pick up the new data next time you open that query. And you only have one place to maintain the content. (Which is why I told you to read up on normalization first - it is a method to allow you to minimize duplication of data in a database.)
 

robsmith

New member
Local time
Today, 12:28
Joined
Feb 17, 2020
Messages
26
Thanks guys, I understand now. And thank you for answering the question that I should’ve been asking!
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 07:28
Joined
Feb 28, 2001
Messages
26,996
No problem, we see that a lot in new Access users. Happy to help.
 

Users who are viewing this thread

Top Bottom