Change table to query?

[Ann Scharpf]

I'm reposting this question from 10-31 as I fear it has slipped through the
cracks...

____

I have a database I used all last year. One of the primary tables in it is
a funding source table. Among the fields on this table are, a project number
(the key) and a funding document number. I have just found out that there
can be MULTIPLE funding documents linked to a single project number, so my
current table design is no good.

Can you please let me know if this plan of attack makes sense:

1. Add another table to the database that will consist of only three
fields: project number, funding document number and funding amount.

2. Write a query that produces an output with the same columns that were in
the original table. The query will calculate the total funding amount for
each project and us the concatenate (function that someone has posted here)
to include all the funding document numbers associated with the project.

3. Edit the SQL of every query that is linked to the old funding source
TABLE and change it to use the new funding source QUERY. Field names will
stay the same, so nothing else should change.

I am still fairly new at building databases and am not sure if anything can
“blow up†with my planned changes. I’d appreciate any feedback you
experienced database designers can give me. Thanks a lot!

 

[mscertified]

It's definitely not advisable to concatenate multiple columns from a table
into a single column in another table. That could cause all kinds of problems
down the line. You should deal with the columns as a one to many relationship
and keep them in their own tables. All of the queries that used your old
table need to now also use also the new table joined with the old table. Any
forms or reports showing the information need to use a subform or subreport
to show the one-to-many data.
This may be more work in the short term but will save you headaches in the
long term.

-Dorian

 

[heifler via AccessMonster.com]

I agree with Dorian and add that concantenation is ok for reporting purposes.
If you just want to sum by project (The one side of a one to many) and sum
the costs of related docs (The many side) you would link on Project id
between the two tables with an outer join so as not to exclude any projects
with no costs yet. Then you group by project and sum on costs. Aggregate
Queries is demonstrated on an Access video tutorial at MyAccessProgram.com,
my site.
Bob Heifler

It's definitely not advisable to concatenate multiple columns from a table
into a single column in another table. That could cause all kinds of problems
down the line. You should deal with the columns as a one to many relationship
and keep them in their own tables. All of the queries that used your old
table need to now also use also the new table joined with the old table. Any
forms or reports showing the information need to use a subform or subreport
to show the one-to-many data.
This may be more work in the short term but will save you headaches in the
long term.

-Dorian

I'm reposting this question from 10-31 as I fear it has slipped through the
cracks...

[quoted text clipped - 24 lines]

“blow up†with my planned changes. I’d appreciate any feedback you
experienced database designers can give me. Thanks a lot!

 

[Ann Scharpf]

Thank you both for responding. I just want to clarify and make sure your
response remains the same...

I was not planning to concatentate multiple COLUMNS. I was planning to
concatenate values in the SAME FIELD from multiple records that share the
same Project ID. Is it still a terrible idea to concatenate?

I knew I could group on Project and sum on Funding Amount (that’s what I was
trying to say in step 2 of my proposed process) but I was not sure how to get
a single funding total and not lose the multiple funding document numbers.
Now that I think about it, it is only on some reports that I need to list the
Funding Document numbers. I could change the reports to look at the Funding
Document table and contatenate the Doc Numbers just on the report.

Am I correct in thinking that I can use the same field names in the “Funding
Query†that are in my original “Funding Table†and then edit in SQL to
redirect my other queries to use the “Funding Query†as their data source?
THEN I would go back to the original “Funding Table†and remove the “Funding
Amount†that is now being calculated as an aggregate in the “Funding Query.â€
In other words, assuming you still tell me that the concatenate in the query
is bad news, here would be my steps to fixing my duplicate records problem:

1. Add another table to the database that will consist of only three
fields: project number, funding document number and funding amount.

2. Write a query that produces an output with the same columns that were in
the original table EXCEPT funding document number.

3. Edit the SQL of every query that is linked to the old funding source
TABLE and change it to use the new funding source QUERY. Field names will
stay the same, so nothing else should change. Omit all references to
funding document numbers in queries.

4. Use the concatenate function in reports to list all the funding document
numbers that comprise the funding totals. Can I do that? Have the report
take most of its data from a query and then link to a table, too, to obtain
the funding document numbers?

Or is this where Dorian meant I’d need the sub-report? Would I have to have
a sub-report that lists all the funding document numbers for a project? If I
had to do that, it would double the length of my reports because most
projects have only one funding document. I would not be able to fit these
reports on a single page anymore if I had to add a line to every project.

Thanks again to both of you for your advice. I definitely want to do this
the right/clean way!

 

[heifler via AccessMonster.com]

4. Use the concatenate function in reports to list all the funding document
numbers that comprise the funding totals. Can I do that?
Answer: Yes, do you know how to open a record set and write a function? If
not, I would be happy to help with that.
Bob

Thank you both for responding. I just want to clarify and make sure your
response remains the same...

I was not planning to concatentate multiple COLUMNS. I was planning to
concatenate values in the SAME FIELD from multiple records that share the
same Project ID. Is it still a terrible idea to concatenate?

I knew I could group on Project and sum on Funding Amount (that’s what I was
trying to say in step 2 of my proposed process) but I was not sure how to get
a single funding total and not lose the multiple funding document numbers.
Now that I think about it, it is only on some reports that I need to list the
Funding Document numbers. I could change the reports to look at the Funding
Document table and contatenate the Doc Numbers just on the report.

Am I correct in thinking that I can use the same field names in the “Funding
Query†that are in my original “Funding Table†and then edit in SQL to
redirect my other queries to use the “Funding Query†as their data source?
THEN I would go back to the original “Funding Table†and remove the “Funding
Amount†that is now being calculated as an aggregate in the “Funding Query.â€
In other words, assuming you still tell me that the concatenate in the query
is bad news, here would be my steps to fixing my duplicate records problem:

1. Add another table to the database that will consist of only three
fields: project number, funding document number and funding amount.

2. Write a query that produces an output with the same columns that were in
the original table EXCEPT funding document number.

3. Edit the SQL of every query that is linked to the old funding source
TABLE and change it to use the new funding source QUERY. Field names will
stay the same, so nothing else should change. Omit all references to
funding document numbers in queries.

4. Use the concatenate function in reports to list all the funding document
numbers that comprise the funding totals. Can I do that? Have the report
take most of its data from a query and then link to a table, too, to obtain
the funding document numbers?

Or is this where Dorian meant I’d need the sub-report? Would I have to have
a sub-report that lists all the funding document numbers for a project? If I
had to do that, it would double the length of my reports because most
projects have only one funding document. I would not be able to fit these
reports on a single page anymore if I had to add a line to every project.

Thanks again to both of you for your advice. I definitely want to do this
the right/clean way!

I agree with Dorian and add that concantenation is ok for reporting purposes.
If you just want to sum by project (The one side of a one to many) and sum

[quoted text clipped - 16 lines]

 

[Ann Scharpf]

--
Ann Scharpf
I have absolutely NO IDEA how to open a record set & write a function! I
would appreciate any help you can give me!

Ann

 

[heifler via AccessMonster.com]

Ann,
See my "Signature"