This has got to be an easy question?

  • Thread starter Thread starter Logo
  • Start date Start date
L

Logo

Is all I want to do is combine two records from a query. I have data from a
query from a lunch account and from a dinner account. All I need to do is
combine the data into one account. There are probably around 10 fields of
data, but they all have the same properties.

Surely this is easier than I'm making it out to be? I tried a union query,
but didn't get what I wanted. This is what I'm looking at-

SELECT *
FROM TableData
WHERE AccountName=(restname);

I want to combine with-

SELECT *
FROM TableData
WHERE AccountName=(restnamelunch);

I want to combine the results into one record named maybe (restnamecombined).
Most of the fields would be summed, except for the name and id#, but a few
of the fields near the end would be averaged. TIA
 
Is either of the AccountName fields the primary key fields for their tables?

What are restname and restnamelunch? Field names? Text?
 
The accountname field is not a primary key.
They are both records from the same table.
One accountname is "Maguires".
The other is "Maguires AM".

They are text, but I guess you could say they are the names of individual
records in the table, not field names.
 
If your query is returning exactly two records

SELECT First(AccountName), First(ID#)
, Sum(Fielda)
, Sum(FieldB)
, Avg(FieldC)
FROM TableData
WHERE AccountName IN ("Maguires", "Maguires AM")


IF you have multiple names like your example and the ID# are different for
Maguires and Maguires AM then you have a problem that could best be solved by
adding an additional table.
Table: SameAccount
AccountMasterNum
AccountID

You would have multiple records in the SameAccount table with for instance
AccountMasterNum AccountID
1 22 (Maguires)
1 34 (Maguires AM)
2 11 (Tobys)
2 23 (Tobys PM)
2 19 (Tobys AM)
3 1 (Charlie)

Now you could use
SELECT First(ID#), First(AccountName),
, Sum(Fielda)
, Sum(FieldB)
, Avg(FieldC)
FROM SameAccount INNER JOIN YourExistingTable
ON SameAccount.AccountID = TableData.[ID#]
GROUP BY SameAccount.AccountMasterNum

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
 
Great idea! I created the new table and got the right results. But my query
left them with just their id#'s. How can I convert the id#'s back into an
account name?
--
Logo


John Spencer said:
If your query is returning exactly two records

SELECT First(AccountName), First(ID#)
, Sum(Fielda)
, Sum(FieldB)
, Avg(FieldC)
FROM TableData
WHERE AccountName IN ("Maguires", "Maguires AM")


IF you have multiple names like your example and the ID# are different for
Maguires and Maguires AM then you have a problem that could best be solved by
adding an additional table.
Table: SameAccount
AccountMasterNum
AccountID

You would have multiple records in the SameAccount table with for instance
AccountMasterNum AccountID
1 22 (Maguires)
1 34 (Maguires AM)
2 11 (Tobys)
2 23 (Tobys PM)
2 19 (Tobys AM)
3 1 (Charlie)

Now you could use
SELECT First(ID#), First(AccountName),
, Sum(Fielda)
, Sum(FieldB)
, Avg(FieldC)
FROM SameAccount INNER JOIN YourExistingTable
ON SameAccount.AccountID = TableData.[ID#]
GROUP BY SameAccount.AccountMasterNum

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
The accountname field is not a primary key.
They are both records from the same table.
One accountname is "Maguires".
The other is "Maguires AM".

They are text, but I guess you could say they are the names of individual
records in the table, not field names.
Click to expand...
.
Click to expand...
 
Please copy and post the SQL of your query.

(Possibly unneeded instructions follow)
Open the query
Select View:Sql from the Menu
Select all the text
Copy it
Paste it into the message

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
 
Great idea! I created the new table and got the right results. But my query
left them with just their id#'s. How can I convert the id#'s back into an
account name?
Click to expand...

Don't confuse data STORAGE with data DISPLAY.

The junction table *should* contain only ID's - no account names, nothing from
the other tables other than the ID. You needn't (shouldn't!) open the query
datasheet and look at it for anything other than debugging.

To see the accountnames on a Form, you can use a Combo box storing the ID and
displaying the name. To see the names on Reports, base the report on a query
pulling fields from all three tables; you would pull the account name from the
accounts table, etc.
 
I wrote it with just one field totaled:

SELECT MasterAcctNo, SUM(Field3) AS [TotalField3]
FROM SameAccountTable
GROUP BY MasterAcctNo;

I'm thinking maybe I should enter a new account name with a new account
number in my original table and link it with the SameAccountTable to get the
new name for the master account created? That would entail adding a
masteracctno field to my original table? TIA
 
Did you add a master account name field to the SameAccountTable?

If not. I would do so.

I would write a query like the following if your structure is as described.
Assumptions:
SameAccountTable has two fields: MasterAcctNo and RelatedAcctNo
OriginalAccountTable has at least an account number, an account name, and an
account amount.

SELECT S.MasterAcctNo
, First(O.AccountName) as RandomAccountName
, SUM(O.Amount) AS TotalAmount
FROM SameAccountTable As S INNER JOIN OriginalAccountTable as O
ON S.RelatedAccountNumber = O.AccountNumber
GROUP BY S.MasterAcctNo;

First(O.AccountName)will return one of the names in the group of names
associated with the Master Account Number. You could also use Last, Max, or
Min to get one of the associated names.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
 
John Spencer said:
Did you add a master account name field to the SameAccountTable?

If not. I would do so.
Click to expand...

Yes, that was the first field in my sameaccount table.
I would write a query like the following if your structure is as described.
Assumptions:
SameAccountTable has two fields: MasterAcctNo and RelatedAcctNo
OriginalAccountTable has at least an account number, an account name, and an
account amount.
Click to expand...

Yes SameAccountTable has those two plus a few more, and also has the
original acct name with it. I guess that's an example of being redundant?
OriginalAccountTable has the account number, name and amount, but doesn't
have the MasterAcctNo.
SELECT S.MasterAcctNo
, First(O.AccountName) as RandomAccountName
, SUM(O.Amount) AS TotalAmount
FROM SameAccountTable As S INNER JOIN OriginalAccountTable as O
ON S.RelatedAccountNumber = O.AccountNumber
GROUP BY S.MasterAcctNo;

First(O.AccountName)will return one of the names in the group of names
associated with the Master Account Number. You could also use Last, Max, or
Min to get one of the associated names.
Click to expand...

Wasn't having much success with First or Min, but using this I did get the
results I needed-

SELECT SameAccountNo.[WorkMonth], [SameAccountNo].MasterAcctNumber,
Sum(SameAccountNo.[TotCashInEnv]) AS SumOfMastTotCashInEnvelope
FROM SameAccountNo
GROUP BY SameAccountNo.[WorkMonth], [SameAccountNo].MasterAcctNumber;

But now I'm stuck on the MasterAcctNo, getting it related to a
MasterAcctName. I'm thinking I should delete the OriginalAcctName field in
the SameAccountTable, and insert a MasterAcctName field?

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
I wrote it with just one field totaled:

SELECT MasterAcctNo, SUM(Field3) AS [TotalField3]
FROM SameAccountTable
GROUP BY MasterAcctNo;

I'm thinking maybe I should enter a new account name with a new account
number in my original table and link it with the SameAccountTable to get the
new name for the master account created? That would entail adding a
masteracctno field to my original table? TIA
Click to expand...
.
Click to expand...
 
Back
Top