combining tables in Access

Here's an example of what I'd like to do. I have four tables which
contain the same items as below. I would like to combine the four
tables so that the companies in all four tables show up with the
corresponding quantities next to them (including those that do not
have a quantity.)

Table 1:
QuantityA Company
10 ABC
11 BCD
12 EFG
13 HIJ
14 KLM
15 ZZZ

Table 2:
QuantityB Company
20 ABC
21 BCD
22 HIJ
23 KLM

Table 3:
QuantityC Company
30 ABC
31 NOP
32 QRS

Table 4:
QuantityD Company
10 TUV
11 WXY
12 ZZZ

This is what I'd like the resulting table to look like:

Combined Table:
QuantityA QuantityB QuantityC QuantityD
Company
10 20 30 ABC
11 21 BCD
12 EFG
13 22 HIJ
14 23 KLM
31 NOP
32 QRS
10 TUV
11 WXY
15 12 ZZZ

Any help would be very much appreciated. Thank you.

Re: combining tables in Access

Perhaps, instead of typing "examples", you could explain what the fields
are, and mean, and someone could give you a worthwhile suggestion. I might
guess that QuantityA is the name of a Field, that Company is the name of a
Field, and that the contents of the first record of Table 1 is a value for
QuantityA of 10 and a value/content of Company is ABC -- but, particularly
given the reformatting that happens to newsgroup posts, I'd only be
guessing.

If my guess is correct, I'd think that you could create a Query with each of
the Tables as data sources, joining on the Company Field, include each
Quantity field, and the Query would give you the output you desire. If
there are multiple records for the same company in each Table, then you'll
need to use a Totals Query. Depending on what you want/need to _do_ with
the information, you may be able to use the Query. If you have a compelling
need to create a table, then you would convert the Query to a MakeTable
query.

If my guess is not correct, then with some explanation, perhaps someone can
suggest a solution.

Larry Linson
Microsoft Access MVP

"JNS" wrote in message
news:dd68380e-a8d8-4f3d-9a81-8ce1bf821d41@e10g2000prf.google groups.com...
> Here's an example of what I'd like to do. I have four tables which
> contain the same items as below. I would like to combine the four
> tables so that the companies in all four tables show up with the
> corresponding quantities next to them (including those that do not
> have a quantity.)
>
> Table 1:
> QuantityA Company
> 10 ABC
> 11 BCD
> 12 EFG
> 13 HIJ
> 14 KLM
> 15 ZZZ
>
> Table 2:
> QuantityB Company
> 20 ABC
> 21 BCD
> 22 HIJ
> 23 KLM
>
> Table 3:
> QuantityC Company
> 30 ABC
> 31 NOP
> 32 QRS
>
> Table 4:
> QuantityD Company
> 10 TUV
> 11 WXY
> 12 ZZZ
>
> This is what I'd like the resulting table to look like:
>
> Combined Table:
> QuantityA QuantityB QuantityC QuantityD
> Company
> 10 20 30 ABC
> 11 21 BCD
> 12 EFG
> 13 22 HIJ
> 14 23 KLM
> 31 NOP
> 32 QRS
> 10 TUV
> 11 WXY
> 15 12 ZZZ
>
> Any help would be very much appreciated. Thank you.

Re: combining tables in Access

Your going about this all wrong, what you want is a table like this

Quantity Type Quantity Company

A 10 ABC

A 20 KLM

B 11 BCD

That way if you add quantity type "E" you don't have to rebuild your table

Re: combining tables in Access

This will have to be a 2 step process.

Create a Union Query First (Query1) to ascertain the quantity type
----------------------------
SELECT "QuantityA" AS [QtyType], QuantityA AS QTY, Company FROM Table1
UNION
SELECT "QuantityB" AS [QtyType], QuantityB AS QTY, Company FROM Table2
UNION
SELECT "QuantityC" AS [QtyType], QuantityC AS QTY, Company FROM Table3
UNION
SELECT "QuantityD" AS [QtyType], QuantityD AS QTY, Company FROM Table4

And then
----------
use a cross-tab query using the QtyType as your headers

(sorry was on way out the door so, haven't tested example yet, just using
off the top of my head, but I'm sure it's the right direction)


Dominic

"JNS" wrote in message
news:dd68380e-a8d8-4f3d-9a81-8ce1bf821d41@e10g2000prf.google groups.com...
> Here's an example of what I'd like to do. I have four tables which
> contain the same items as below. I would like to combine the four
> tables so that the companies in all four tables show up with the
> corresponding quantities next to them (including those that do not
> have a quantity.)
>
> Table 1:
> QuantityA Company
> 10 ABC
> 11 BCD
> 12 EFG
> 13 HIJ
> 14 KLM
> 15 ZZZ
>
> Table 2:
> QuantityB Company
> 20 ABC
> 21 BCD
> 22 HIJ
> 23 KLM
>
> Table 3:
> QuantityC Company
> 30 ABC
> 31 NOP
> 32 QRS
>
> Table 4:
> QuantityD Company
> 10 TUV
> 11 WXY
> 12 ZZZ
>
> This is what I'd like the resulting table to look like:
>
> Combined Table:
> QuantityA QuantityB QuantityC QuantityD
> Company
> 10 20 30 ABC
> 11 21 BCD
> 12 EFG
> 13 22 HIJ
> 14 23 KLM
> 31 NOP
> 32 QRS
> 10 TUV
> 11 WXY
> 15 12 ZZZ
>
> Any help would be very much appreciated. Thank you.

Re: combining tables in Access

Thanks for your input Larry. I'm sorry I didn't explain my example,
but your guess is correct. QuantityA, QuantityB, QuantityC, QuantityD
are all fields, and Company is a field as well. The numbers are the
values for the Quantity fields, and "ABC" is the content of the
Company field. The end table is one which I would like to have the
fields QuantityA, QuantityB, QuantityC, QuantityD, and Company with
each corresponding quantity with all the company names listed.

I tried your suggestion of joining on the company field and included
each Quantity field. It almost provided the output I wanted. The
resulting table had all the fields: QuantityA, QuantityB, QuantityC,
QuantityD, and Company with the quantities from each of the four
tables. But if QuantityA and QuantityC had a value for Company: ABC,
and QuantityB did not have any info because it does not have company
ABC in the table, the resulting table QuantityC field shows up as "0"
because QuantityB did not have any info on company ABC. I hope this
makes sense.

I'm not too familiar with Access, so would you be able to provide me
with a little more detail? I'm not sure if I am going about this
correctly, but this is what I did.
1) I selected "New Query" design view.
2) For "Show Table" I added each of the four tables.
3) I joined each of the tables by the "Company" field.
4) Between each of the tables, I selected "Join Properties"
5) For each "Join Properties" selection, I selected the Left Table
Name and the Right Table Name, and the second option "Include ALL
records from Table 1 and only those records of Table 1 where the
joined fields are equal."
6) I included each of the Quantity fields and Company field.
7) The resulting table shows each of the quantity fields and the
company fields.

Here's a problem I ran into. If:
Table1: QuantityA=10, Company: ABC
Table2: Quantity B=N/A, Company: does not include ABC
Table3: QuantityC=20, Company: ABC
Table4: QuantityD=30, Company: ABC
The resulting table with the join property query created above shows:
QuantityA=10, QuantityB=0, QuantityC=0, QuantityD=0, Company: ABC

But if Table 2 includes a quantity (example: 15) for QuantityB, and
includes company ABC, the resulting table will show all quantities.
QuantityA=10, QuantityB=15, QuantityC=20, QuantityD=30, and Company
ABC.

It seems that if there is a quantity for ABC in Table1 and Table3, and
no information on ABC for Table2, it will not show the quantity to
Table3 because of the way the join property is set up. Table1 has a
quantity for QuantityA for Company ABC and Table2 does not have any
information on company ABC. Table3 has a quantity for QuantityC for
company ABC. But since the join property between Table2 and Table3
only joins those that are included in Table2, even though Table1 and
Table3 have a quantity field for company ABC, the quantity for Table3
gets left out since there was no information on it on Table2. Is
there a way to set up the join property so that all the quantities
show up? I hope this makes sense.

What I want to create is a table where all the company names in all
four tables show up, and have each corresponding quantity for each
with four quantity fields. I just want to make sure none of the
quantities gets left out.

Re: combining tables in Access

As I said in an earlier post, you need to change the set up and only have
one table.

"JNS" wrote in message
news:82053c82-6968-444b-b647-b321d7eee732@s8g2000prg.googleg roups.com...
> Thanks for your input Larry. I'm sorry I didn't explain my example,
> but your guess is correct. QuantityA, QuantityB, QuantityC, QuantityD
> are all fields, and Company is a field as well. The numbers are the
> values for the Quantity fields, and "ABC" is the content of the
> Company field. The end table is one which I would like to have the
> fields QuantityA, QuantityB, QuantityC, QuantityD, and Company with
> each corresponding quantity with all the company names listed.
>
> I tried your suggestion of joining on the company field and included
> each Quantity field. It almost provided the output I wanted. The
> resulting table had all the fields: QuantityA, QuantityB, QuantityC,
> QuantityD, and Company with the quantities from each of the four
> tables. But if QuantityA and QuantityC had a value for Company: ABC,
> and QuantityB did not have any info because it does not have company
> ABC in the table, the resulting table QuantityC field shows up as "0"
> because QuantityB did not have any info on company ABC. I hope this
> makes sense.
>
> I'm not too familiar with Access, so would you be able to provide me
> with a little more detail? I'm not sure if I am going about this
> correctly, but this is what I did.
> 1) I selected "New Query" design view.
> 2) For "Show Table" I added each of the four tables.
> 3) I joined each of the tables by the "Company" field.
> 4) Between each of the tables, I selected "Join Properties"
> 5) For each "Join Properties" selection, I selected the Left Table
> Name and the Right Table Name, and the second option "Include ALL
> records from Table 1 and only those records of Table 1 where the
> joined fields are equal."
> 6) I included each of the Quantity fields and Company field.
> 7) The resulting table shows each of the quantity fields and the
> company fields.
>
> Here's a problem I ran into. If:
> Table1: QuantityA=10, Company: ABC
> Table2: Quantity B=N/A, Company: does not include ABC
> Table3: QuantityC=20, Company: ABC
> Table4: QuantityD=30, Company: ABC
> The resulting table with the join property query created above shows:
> QuantityA=10, QuantityB=0, QuantityC=0, QuantityD=0, Company: ABC
>
> But if Table 2 includes a quantity (example: 15) for QuantityB, and
> includes company ABC, the resulting table will show all quantities.
> QuantityA=10, QuantityB=15, QuantityC=20, QuantityD=30, and Company
> ABC.
>
> It seems that if there is a quantity for ABC in Table1 and Table3, and
> no information on ABC for Table2, it will not show the quantity to
> Table3 because of the way the join property is set up. Table1 has a
> quantity for QuantityA for Company ABC and Table2 does not have any
> information on company ABC. Table3 has a quantity for QuantityC for
> company ABC. But since the join property between Table2 and Table3
> only joins those that are included in Table2, even though Table1 and
> Table3 have a quantity field for company ABC, the quantity for Table3
> gets left out since there was no information on it on Table2. Is
> there a way to set up the join property so that all the quantities
> show up? I hope this makes sense.
>
> What I want to create is a table where all the company names in all
> four tables show up, and have each corresponding quantity for each
> with four quantity fields. I just want to make sure none of the
> quantities gets left out.

Re: combining tables in Access

My further guess is that the fields QuantityA, QuantityB, QuantityC, and
QuantityD are quantities of four different items, the item identified by the
column name. You are mixing data with structure, or, as it is often
described in the Access world, "comitting spreadsheet". I can create
queries to do what I think you want (though, again, when you give examples
in a newsgroup and your no-doubt carefully-done formatting is lost between
you and the reader, it would not be certain), but that is not simple, and I
am reluctant to lead anyone else down what I view as the "wrong path".

I agree with Scott that you need to restructure your data, but based on the
above assumption, I suggest two (or, even better, three) tables, if there's
probability you will use this database on a continuing basis (because sooner
or later, you are going to want to do more with it than you realize today):

Table Companies, with a Field CompanyID identifying the Company, and any
other fields with information about the company, such as name, address,
etc..

Table Items, with a field ItemID identifying the Item, and any other fields
with information about the item, such as name, description, etc..

Table CompanyItem, with fields CompanyID to indicate the Company and ItemID
to indicate the item, and Quantity.

To create the Report or Form you describe, you'd use a Cross-Tab Query in
any version of Access, or in recent Versions, a Pivot Table. That's not the
easiest thing to create, though.

I'd guess you can, more simply, do what Scott suggests:

Table tblEverything

Fields: Company, QuantityA, QuantityB, QuantityC, QuantityD

Fill the Company field with each company name/ident.

Manually execute a Query joining on Company name from tblEverything to
Table1, "all records from tblEverything and those that match from Table1",
convert it to an Update Query and update the QuantityA field with the value
from Table1. Repeat, using the appropriate fields from Table2, Table3, and
Table4.

To avoid seeing "0" when there is no corresponding item for a QuantityA,
QuantityB, QuantityC, QuantityD, use a Format statement, with a "" in the
part that formats zero values.

I believe you'll find some articles on relational design at some of the
resource sites listed at http://mail.ntpcug.org/accesssig/default.aspx.
There are some good basic Access online training courses to which you can
link from http://office.microsoft.com. Just stumbling along, and then
having to come to the newsgroup for advice on how to try to recover from an
inappropriate design that you've already implemented is definitely the hard
way to learn.

Larry Linson
Microsoft Access MVP




"JNS" wrote in message
news:82053c82-6968-444b-b647-b321d7eee732@s8g2000prg.googleg roups.com...
> Thanks for your input Larry. I'm sorry I didn't explain my example,
> but your guess is correct. QuantityA, QuantityB, QuantityC, QuantityD
> are all fields, and Company is a field as well. The numbers are the
> values for the Quantity fields, and "ABC" is the content of the
> Company field. The end table is one which I would like to have the
> fields QuantityA, QuantityB, QuantityC, QuantityD, and Company with
> each corresponding quantity with all the company names listed.
>
> I tried your suggestion of joining on the company field and included
> each Quantity field. It almost provided the output I wanted. The
> resulting table had all the fields: QuantityA, QuantityB, QuantityC,
> QuantityD, and Company with the quantities from each of the four
> tables. But if QuantityA and QuantityC had a value for Company: ABC,
> and QuantityB did not have any info because it does not have company
> ABC in the table, the resulting table QuantityC field shows up as "0"
> because QuantityB did not have any info on company ABC. I hope this
> makes sense.
>
> I'm not too familiar with Access, so would you be able to provide me
> with a little more detail? I'm not sure if I am going about this
> correctly, but this is what I did.
> 1) I selected "New Query" design view.
> 2) For "Show Table" I added each of the four tables.
> 3) I joined each of the tables by the "Company" field.
> 4) Between each of the tables, I selected "Join Properties"
> 5) For each "Join Properties" selection, I selected the Left Table
> Name and the Right Table Name, and the second option "Include ALL
> records from Table 1 and only those records of Table 1 where the
> joined fields are equal."
> 6) I included each of the Quantity fields and Company field.
> 7) The resulting table shows each of the quantity fields and the
> company fields.
>
> Here's a problem I ran into. If:
> Table1: QuantityA=10, Company: ABC
> Table2: Quantity B=N/A, Company: does not include ABC
> Table3: QuantityC=20, Company: ABC
> Table4: QuantityD=30, Company: ABC
> The resulting table with the join property query created above shows:
> QuantityA=10, QuantityB=0, QuantityC=0, QuantityD=0, Company: ABC
>
> But if Table 2 includes a quantity (example: 15) for QuantityB, and
> includes company ABC, the resulting table will show all quantities.
> QuantityA=10, QuantityB=15, QuantityC=20, QuantityD=30, and Company
> ABC.
>
> It seems that if there is a quantity for ABC in Table1 and Table3, and
> no information on ABC for Table2, it will not show the quantity to
> Table3 because of the way the join property is set up. Table1 has a
> quantity for QuantityA for Company ABC and Table2 does not have any
> information on company ABC. Table3 has a quantity for QuantityC for
> company ABC. But since the join property between Table2 and Table3
> only joins those that are included in Table2, even though Table1 and
> Table3 have a quantity field for company ABC, the quantity for Table3
> gets left out since there was no information on it on Table2. Is
> there a way to set up the join property so that all the quantities
> show up? I hope this makes sense.
>
> What I want to create is a table where all the company names in all
> four tables show up, and have each corresponding quantity for each
> with four quantity fields. I just want to make sure none of the
> quantities gets left out.

Re: combining tables in Access

Thank you all for your help. I appreciate all your comments and input
which were very useful and valuable. After much trial and error this
week, I was able to use your (Dominic) suggestion to get the output I
wanted. In the process, I learned much valuable knowledge of MS
Access. Thanks a lot everyone!