Is it possible to have the data of two or more fields in one column in a query

[annakalli]

I am trying to have the data of two fields in one column for instance to have the invoices field data and the payment field data in the same column and not the one next to the other, in other words i am asking if it is possible to have two fieds in the same field cell in the field row of the query.

[gnawoncents]

Are you trying to combine the contents of these two fields into a new field on a table, form, query, ...? Please give an example of what you would like.

[annakalli]

I am trying to create a statement for a customer, i want to use the field invoice number from the invoice table and payment number from the payments table and have them in one culumn called customer transactions sorted by the date

[gnawoncents]

Sure, this is possible.

1. Create an update query
2. Add the "invoice table" and "payments table"
3. Select the field they both have in common and create a join (drag field from one table to matching field on other table)
4. Select the [customer transaction] field and put the following code in the "Update To:" cell:
[invoice table].[field invoice number] & [payments table].[payment number]
5. Run the query

That should do it for you so long as you have a field with matching data (such as a customer ID field or something) in both tables.

[annakalli]

Thanks for ur advise,I select the common field which is the customer code and i try it but it doesnt work because in the invoices table i may have many records for a customer (one record for every purchase invoice) and may be only one or even none records for that customer in the payments table, depents if we paid him or not. Any more ideas please?

[gnawoncents]

Here’s another option. Assuming your date column is named “Invoice Date,” paste the following into the SQL view of your query and I think you’ll get what you’re looking for. If not, please let me know.


SELECT [invoice table].[Invoice Date], [invoice table].[customer code], [field invoice number] & [payment number] AS [Customer Transaction]
FROM [payments table] RIGHT JOIN [invoice table] ON [payments table].[customer code] = [invoice table].[customer code]
ORDER BY [invoice table].[Invoice Date];


This will display all the invoices and if they do not have a matching payment number, then the customer transaction field will simply display the field invoice number again.

[annakalli]

Thanks a lot, finally is working.

[gnawoncents]

Glad it's working for you!

[annakalli]

Dear friend, i am wondering if i can have as well the following:

example
invoices table
invoice number customer code date amount
0001 2030400 10/5/10 450
0002 2030401 03/5/10 500

payments table
payment number customer code date amount
1000 2030400 17/5/10 200
1001 2030401 20/5/10 120
1002 2030400 25/5/10 100

and have the following resut
customers transactions form
customer code 2030400
date description amount
10/5/10 inv 0001 450
17/5/10 pay 1000 200
25/5/10 pay 1002 100
Total 150

Thank u in advance

[gnawoncents]

annakalli,

This shouldn't be too difficult, but you will need to create few things in your database.

Make a table named tblCustomerTran sactions

Add the following fields and types:

ID / AutoNumber
customer code / Number
transaction date / Date/Time
description / Text
amount / Currency

NOTE: set description field to Indexed: Yes (no duplicates)

Create a new query named qryAppendPaymen ts

Add the payments table

Change it to an append query

Select the appropriate fields from payments table to update the fields in tblCustomerTran sactions. Only one will be tricky. Since you want to add some text to differentiate payments from invoices, after you set up the [payment number] field, change the Field: cell to read as listed below.

pay: "pay " & [payment number]

This will add the “pay” text before each payment number.

Next you will have to create an append query for the invoices table in the same manner.

When you create the customer transactions form, set its record source to the tblCustomerTran sactions table.

Set both append queries to run every time the form is opened (make sure to set warnings to FALSE or this will get really annoying fast). Since you set the description field to index with no duplicates you won’t have to worry about adding the same records over and over.

The rest should be easy. Let me know if you run into any snags.

[annakalli]

Dear friend,
I am really very thankful for all ur advices, everything worked very well, instead of the field amount i create 2 fields called DEBIT and CREDIT and i append the payment amount field in the DEBIT and the Invoice amount field in the CREDIT, and is exactly what i wanted, but when create the customers transaction form i did'nt know how to do the following
"Set both append queries to run every time the form is opened" and as well if it is possible to have in my form the field opening balance of the customer which is located in the table called accounts and have the following

customer name: BBBBB
customer code:50001
opening balance:330
date description debit credit balance
3/5/10 purchases 300 630
5/5/10 payment 100 530
8/5/10 purchases 200 730


so far my form looks like the above exept from the balance column, i cant work it out how to the balance column since it has to use the balance amount of the previous row and add in this amount the credit amount or delete the debit amount of the current row.

Thank u in advance.

[gnawoncents]

To run the queries each time the form opens, add the code below to the form’s “Form Open” event.

     
‘Update the [name of your table] table
 DoCmd.SetWarnings False       ‘This disables verification popup boxes
 DoCmd.OpenQuery ("qryAppendPayments")
DoCmd.OpenQuery ("qryAppendInvoices")
 DoCmd.SetWarnings True       ‘This enables verification popup boxes

So long as there is only one entry for each customer in the accounts table, you can get the value for the “opening balance” using the dlookup function with customer code as the matching criteria.

As far as setting a running total in the balance field, it would be far easier to have a single field that consistently shows only the current balance. Then it would be a simple matter of setting its value to the opening balance + invoices – payments.

If you must have a running total calculated for each transaction, I’m sure there is a way to do this, but don’t know how offhand.

[annakalli]

Hi, the codes worked ok, after i tried to create a report called "accounts1" that would display the data of the tblcustomerstra nsactions and as well to retrieve the opening balance of each customer. I insert a text box and in the control source i enter the following code

=DLookup("[opening balance]", "[accounts]", "[Accountnumber] = " & reports![accounts1]![account code])

but i am always getting an error message that is something wrong with my code, and i cant figure it out, can u please help me once again.

the "opening balance" field belongs to the table called "accounts", and the field for the customer code in the table "accounts" is "accountnum ber" and in my report the relevant field is "account code".

[gnawoncents]

@annakalli,

You have an extra set of brackets in your DLookup. The table name only needs the quotation marks. Try:

=DLookup("[opening balance]", "accounts", "[Accountnumber] = " & reports![accounts1]![account code])

I can't be sure this is what's causing the problem as you didn't say what error you're getting. If you're still having problems, let us know.

[annakalli]

I try it but still, i get the same error message
"the expression you entered contains invalid syntax"