Automatic Data Transfer

[Guest]

I have created a system with a table having multiple records, each having
multiple numerical data fields. What i want to do is to combine the totals of
each of these fields across the records and have the combined totals put in
to a second table as a single record entry.

E.G. - First table

ID* | Option 1 | Option 2 | Option 3
1 | 2 | 3 | 5
2 | 4 | 7 | 2

E.G. - Second Table

ID* | Option 1 | Option 2 | Option 3
1 | 6 | 10 | 7


I have found that I can create individual summary queries for each of the
fields (i'm talking a lot of fields - about 40-45) but I cannot create a
single query that summerises all the fields at the same time. Also i have
found that when i try to link multiple summary queries in to a single form
which also has a different table feeding in to it that it will not allow such
a data source to exist.

I know there is a way to use VB code to pull data from a form but what I
would like to know is if there is a VB coding i could use to pull the data
from the queries and put them all together as a single record in the new
table. For example i could have the VB code run automatically when the data
entry form for the table is closed. I do not mind having to put a single
entry in for each query to pull the data to the table.

E.G. - VB Code

(Table 2, Record 1, Field 'abc').value = (data from query one)

To allow for a better simulation prospect for any helpers, both of the
tables (original and summary) have the same field layout and names. Three of
the fields are called:

"Shirts 15"
"Shirts 15-5"
"Shirts 16"

The fields could be renamed if VB is not happy with the spaces.

I have queries for each of those fields which summarise the data in them as
a single total:

"Sum of Shirts 15"
"Sum of Shirts 15-5"
"Sum of Shirts 16"

So in the format I am looking for:

Table!'Table2'!'Record 1'!'Shirts 15'.value = Query!'Sum of Shirts 15'!'Sum
of Shirts 15'.value

That is the style i am looking for but it has been a long while since i did
any coding on VB and thus I am unable to remember any methods for doing such
a task. Thank you in advance to anyone willing to help.

 

[Guest]

About combining 2 table with similiar data. In general it should be in the
same table or at least have a key to a table higher up so that you have a way
of knowing the data you need. Otherwise you'd need to do a union query to
make these 2 table in to 1 table. Forms and reports accept only 1 record
source. If you need more than 1 table as your record source you need to use a
query. You may have a chains of queries and tables all combining to make your
final query that can be your record source or have to program it in vba.
Please understand this is a relational data base and the data that you are
putting together for a form or a report needs to be related. There are tons
of websites explaining how to set up a relational database. Please read them.


If u just want a summation of your options use the summation button (looks
like a sideways M). The totals probably dont need to stored bec. they can be
figured out on the form or report. Once the data is normalized I doubt highly
you'll need to use much VBA to get the data the way you want it. BTW, To get
a query written for u on this board probably would need to post your table
layouts with Keys.

HTH
Martin J

 

[Guest]

Thanks for your help, I have now managed to solve the problem and have the
database functioning exactly as wanted. The problem i was having is that with
48 fields - the query 'wizard' cannot support such a long list for
summarising - what i did was split the list in to three parts and summarised
each of those as a query then created a main query with all the three
sumarised parts together. This was then linked in to a full query which
brought the three other tables required to join the new data. I know i
proberably could have skipped the step of joining all three together to a
single file first but i found it easier to manage.

On a side note - MS Access WILL allow more than one table to be used on a
single form, if you use the wizard to get the data together in the first
place you can select all the items you want from the first table - then use
the drop down list box to select the next table and then choose the list and
so on. The problem i was previously having is that i was using that method
with three normal tables and then trying to link in a query at the same time
and Access wouldn't allow that combination.

Thanks for the help.

James

 

[Guest]

Thanks for your help, I have now managed to solve the problem and have the
database functioning exactly as wanted. The problem i was having is that with
48 fields - the query 'wizard' cannot support such a long list for
summarising - what i did was split the list in to three parts and summarised
each of those as a query then created a main query with all the three
sumarised parts together. This was then linked in to a full query which
brought the three other tables required to join the new data. I know i
proberably could have skipped the step of joining all three together to a
single file first but i found it easier to manage.

On a side note - MS Access WILL allow more than one table to be used on a
single form, if you use the wizard to get the data together in the first
place you can select all the items you want from the first table - then use
the drop down list box to select the next table and then choose the list and
so on. The problem i was previously having is that i was using that method
with three normal tables and then trying to link in a query at the same time
and Access wouldn't allow that combination.

It is building a query for you on the fly. If you look at the record source
you will see the query.

Martin J