Use a union query to change your data from a spreadsheet to a normalized
Access database table.
Table1CustIDs ---
SELECT [Table 1].CustName, [Table 1].ID1
FROM [Table 1]
WHERE ((([Table 1].ID1) Is Not Null))
UNION ALL SELECT [Table 1].CustName, [Table 1].ID2
FROM [Table 1]
WHERE ((([Table 1].ID2) Is Not Null))
UNION ALL SELECT [Table 1].CustName, [Table 1].ID3
FROM [Table 1]
WHERE ((([Table 1].ID3) Is Not Null))
UNION ALL SELECT [Table 1].CustName, [Table 1].ID4
FROM [Table 1]
WHERE ((([Table 1].ID4) Is Not Null))
UNION ALL SELECT [Table 1].CustName, [Table 1].ID5
FROM [Table 1]
WHERE ((([Table 1].ID5) Is Not Null));
Then use the union query joined to the SystemTable in a totals query ---
SELECT Table1CustIDs.CustName, Sum(SystemTable.[Sales$]) AS [SumOfSales$]
FROM Table1CustIDs INNER JOIN SystemTable ON Table1CustIDs.ID1 =
SystemTable.ID1
GROUP BY Table1CustIDs.CustName;
--
KARL DEWEY
Build a little - Test a little
deeds said:
Sorry....this should do it.....thanks!
Table1:
CustName ID1 ID2 ID3 ID4 ID5
Joe 52 53 54 55 56
Jim 42 43 44
Sam 32 33
SystemTable:
CustName ID1 Sales$
Joe' Co 52 500
Joey Co 53 500
Jim 42 100
Jim's Co 43 200
Jimmy's 44 150
Sam 32 300
Sam's Co 33 400
So I want to simply run a query linking the two tables together using the ID
field.
Joe = 1000
Jim = 450
Sam = 700
:
Post sample data in the manner I asked --
Table1:
CustName ID1 ID2 ID3
Sam X X Y
Bill 0 2 7
SystemTable:
CustName ID Sales
Bill X 49
Sam Y 62
--
KARL DEWEY
Build a little - Test a little
:
Ok..sorry...here it is.... Systemtable does have cust name...however it may
have 3-4 diff cust names with 3-4 diff ID's.
Example: Jim 1234 JimB 4321 JimmyB 2341
Now in Table1...user ENTERS a name (on a form) and selects up to 5 ID's.
Now, I have ONE cust name related to up to 5 ID's. Now, I want to query the
Systemtable USING the Table1 ID's. But when I build query...I bring in both
tables....I see 5 ID's on Table 1 and ONE ID on SystemTable. I want to sum
(from SystemTable) all 5 ID's from the Table1...
Let me know if you need more. Thanks again...hope this helps.
:
I asked for sample data - like this --
Table1:
CustName ID1 ID2 ID3
Sam X X Y
Bill 0 2 7
SystemTable:
CustName ID Sales
Bill X 49
Sam Y 62
If I saw your data I might understand more. As it is I do not know why you
can not just sum the SystemTable as it has the CustName already.
--
KARL DEWEY
Build a little - Test a little
:
Table1: SystemTable:
Field1:CustName Field1:CustName
Field2:ID1 Field2:ID
Field3:ID2 Field3:Sales
Field4:ID3
I want to build query that sums Sales using Table1 ID criteria. So, I want
to somehow link ID1, ID2, ID3 to ID from SystemTable. In other words I want
to sum sales of 3 ID's that resides in Table1 ID's.
Hope this helps.
Thanks
:
What field do you want to sum?
Post sample date from both tables. Also post example of what you expect to
see as a result of the query.
--
KARL DEWEY
Build a little - Test a little
:
Let me add this....
Table1:
Field1:Cust Name
Field2:ID1
Field3:ID2
Field4:ID3
SystemTable:
Field1:Cust Name
Field2:ID
Now...I want to sum the SystemTable using Table1 as the grouping. So, I
want to link Field2(SystemTable) to Field2,Field3,Field4 (Table1).
John Doe may have ID 4444 & 5555 in Table1...I want all John Doe from the
System table and I want both ID 4444 & 5555.
Thanks again!
:
The problem with your system is that John Doe 12345 Elm St, John J. Doe
1234 Elm Street, and J. Doe 1234 Elm Street SW could all have ID1.
So, all records with ID1 will be pulled together.
Each customer needs to have a unique ID or IDs that others do not have.
--
KARL DEWEY
Build a little - Test a little
:
The customer table is a table which is maintained by user: example: User
Keys in customer name then chooses up to 6 ID's that already exist in the
system. So, now I have a table with a user entered "name" and up to 6 ID's
related to that customer. Now, I want to go to the system table and pull all
sales for all of those ID's...to get total by customer.
Customer table:
Field 1: Customer Name
Field 2: ID1
Field 3: ID2
Field 4: ID3
Field 5: ID4
Field 6: ID5
Filed 7: ID6
Thanks....
:
When you say "each customer may have [multiple] different IDs...", how do
you know this? That is, how do you know it is the same customer if the IDs
are different?
Whatever you're using to claim that the customer is the "same", you could
use that to find related IDs.
By the way, are the following "customers" the same:?
John Doe 12345 Elm St
John J. Doe 1234 Elm Street
J. Doe 1234 Elm Street SW
Good luck!
Regards
Jeff Boyce
Microsoft Office/Access MVP
I have a system table with Sales$ etc... Each customer may have 3-4
different ID's listed in this table. I want to combine all of those ID's
and
produce a total for that one customer. I have another table with the ID's
listed as fields. e.g. ID1 ID2 ID3 ID4 etc. Ultimately I want to create
a
query and join the ID field from the system table to 3 or 4 of the ID
fields
from the customer table. How to do this? Thanks in advance.
