Multiple fields in one table need to join

[Kathleen Badeau]

I have a grant funding database. In the database, from a single funding source, I can have up to 20 Grantees (all different) funded. I've named the 20 fields, Grantee 1, Grantee 2, etc up to 20. I now want to combine these grants into one field so that I can do a reports as to which Grantee has gotten funding and from where.

The only thing I can think of is a union query but it will "seem" to take forever doing it that way. Is there not a better and more efficient way of doing the combining?

As for the why I did it this way...it was the only way I could think of at that time.

 

[Fizzio]

You have just learned a good lesson on why normalisation is so important.
Rather than trying to muddle through with your existing data structure, normalise your data as it will cause a lot less headaches.

Sketch out what relationships you want between sets of data and try to normalise the data. You can retrospectively structure the Db by using append/maketable queries to give you a better structure and make the Db more robust and futureproof.

 

[Kathleen Badeau]

It would be better for me (in the long run) to make this two tables...the first table being the original funding information and the second being the breakout of where this funding went?

 

[Kathleen Badeau]

Sorry but the first time I posted I got an error that it was unable to post my message as the system was limited to a certain number of posts done at the same time.