Result from Multiple Fields

  • Thread starter Thread starter Kiran
  • Start date Start date
K

Kiran

Hi,

Loan# Order1 Recd1 Sent1 Order2 Recd2 Sent2
102922978 1/1/2009 1/2/2009 1/3/2009 2/3/2009 2/7/2009 2/12/2009
102929247 1/5/2009 1/8/2009 1/10/2009 2/4/2009 2/6/2009 2/8/2009
103523244 1/5/2009 1/8/2009 1/10/2009


For the above data, I need the output as below

Loan# Order Recd Sent
102922978 1/1/2009 1/2/2009 1/3/2009
102929247 1/5/2009 1/8/2009 1/10/2009
102922978 2/3/2009 2/7/2009 2/12/2009
102929247 2/4/2009 2/6/2009 2/8/2009
103523244 1/5/2009 1/8/2009 1/10/2009

I can get the result with three queries, can it be done using a single query.
 
You need to use a union query since your table structure is incorrect.
Union queries can only be written in the SQL window. You cannot write a
union query using the design view.

SELECT [Loan#], Order1, Recd1, Sent1
FROM [YOUR Table]
UNION ALL
SELECT [Loan#], Order2, Recd2, Sent2
FROM [YOUR Table]


A better table structure would be more like
Loan#
OrderNumber (1,2,3, etc.)
Order
Recd
Sent

You would have one record for each combination of Loan# and OrderNumber.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================
 
Hi,
Thanks for the query, I have a clarification regarding the structure
of the table. For a Single Loan there can be many updates, if we have
different order numbers then do we need to create multiple records for the
same loan, also would it be possible to view all the updates in a single form
as its happening now.I am trying to build my muscle in Access, so if my
question looks stupid please don't mind.

Thanks
Kiran

John Spencer said:
You need to use a union query since your table structure is incorrect.
Union queries can only be written in the SQL window. You cannot write a
union query using the design view.

SELECT [Loan#], Order1, Recd1, Sent1
FROM [YOUR Table]
UNION ALL
SELECT [Loan#], Order2, Recd2, Sent2
FROM [YOUR Table]


A better table structure would be more like
Loan#
OrderNumber (1,2,3, etc.)
Order
Recd
Sent

You would have one record for each combination of Loan# and OrderNumber.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Hi,

Loan# Order1 Recd1 Sent1 Order2 Recd2 Sent2
102922978 1/1/2009 1/2/2009 1/3/2009 2/3/2009 2/7/2009 2/12/2009
102929247 1/5/2009 1/8/2009 1/10/2009 2/4/2009 2/6/2009 2/8/2009
103523244 1/5/2009 1/8/2009 1/10/2009


For the above data, I need the output as below

Loan# Order Recd Sent
102922978 1/1/2009 1/2/2009 1/3/2009
102929247 1/5/2009 1/8/2009 1/10/2009
102922978 2/3/2009 2/7/2009 2/12/2009
102929247 2/4/2009 2/6/2009 2/8/2009
103523244 1/5/2009 1/8/2009 1/10/2009

I can get the result with three queries, can it be done using a single query.
Click to expand...
Click to expand...
 
Your table structure should PROBABLY be something like
Loans
LoanNumber (Primary Key)Don't use the # sign. It is used to delimit dates)
CustomerID (perhaps)
DateOfLoan
<<other fields about the loan>>

ORDERS table
OrderID (Autonumber - Primary Key)
LoanNumber (one of the values in Loans.LoanNumber)
OrderNumber (If you need to track this)
OrderDate
Received
Sent

For data entry you would have a form with a subform on the main form
The form would be a single view form attached to the Loans table (or
better a select query of the Loans table).

The subform would use a select query against the orders table.

For reports you would use a query as the source. The query would have
both the Loans table and the Orders table in it. You would join the two
tables on the LoanNumber. The simple query below would show one row for
each combination of Loans and Orders. If there should be a loan with NO
orders, you would get one row that would have all the loans data and the
orders data would all be blank.

SELECT Loans.*, Orders.*
FROM Loans LEFT JOIN Orders
On Loans.LoanNumber = Orders.LoanNumber


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Hi,
Thanks for the query, I have a clarification regarding the structure
of the table. For a Single Loan there can be many updates, if we have
different order numbers then do we need to create multiple records for the
same loan, also would it be possible to view all the updates in a single form
as its happening now.I am trying to build my muscle in Access, so if my
question looks stupid please don't mind.

Thanks
Kiran

John Spencer said:
You need to use a union query since your table structure is incorrect.
Union queries can only be written in the SQL window. You cannot write a
union query using the design view.

SELECT [Loan#], Order1, Recd1, Sent1
FROM [YOUR Table]
UNION ALL
SELECT [Loan#], Order2, Recd2, Sent2
FROM [YOUR Table]


A better table structure would be more like
Loan#
OrderNumber (1,2,3, etc.)
Order
Recd
Sent

You would have one record for each combination of Loan# and OrderNumber.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Hi,

Loan# Order1 Recd1 Sent1 Order2 Recd2 Sent2
102922978 1/1/2009 1/2/2009 1/3/2009 2/3/2009 2/7/2009 2/12/2009
102929247 1/5/2009 1/8/2009 1/10/2009 2/4/2009 2/6/2009 2/8/2009
103523244 1/5/2009 1/8/2009 1/10/2009


For the above data, I need the output as below

Loan# Order Recd Sent
102922978 1/1/2009 1/2/2009 1/3/2009
102929247 1/5/2009 1/8/2009 1/10/2009
102922978 2/3/2009 2/7/2009 2/12/2009
102929247 2/4/2009 2/6/2009 2/8/2009
103523244 1/5/2009 1/8/2009 1/10/2009

I can get the result with three queries, can it be done using a single query.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top