Query from two tables

[willkr]

I'm trying to create a report that shows both deposits and withdrawals from an account on a single report. The Deposits are stored in one table and the Withdrawals are stored in a different report. Can someone show me a query that extracts from two separate tables?

willkr

[pbaldy]

I would probably have had one table. You'll need a UNION query to join them:

https://support.microsoft.com/en-us/...0-ad0a75541c6e

[June7]

Agree, one "Transactions" table probably would have been better. UNION query can generate this dataset to mimic table it should have been. Calculate fields to provide transaction type info. Example:

SELECT ID, Account, Amount, "D" AS TnxType FROM Deposits
UNION SELECT ID, Account, Amount * -1, "W" FROM Withdrawals;

[willkr]

I agree with both of you. I would be better to have them in a single table. I guess I will have to invest the time to change the database and the app.

One of the issues I'm going to have is how to combine the two tables into one with all the historical data.

[June7]

Do these tables have dependent tables?

[DevilDawg]

I would design a new table that encompasses what you wish the single table to be. You can then create an insert query build using the union of the two tables and just run it. If you can provide the dabase with just the two tables you wish to join with some sample data, I would be more than happy to help you get this done.

[Welshgasman]

I agree with both of you. I would be better to have them in a single table. I guess I will have to invest the time to change the database and the app.

One of the issues I'm going to have is how to combine the two tables into one with all the historical data.

Are the tables identical in regard to field names/positions.

[willkr]

The tables have slightly different forms. There is no real data in them currently, so I don't need to save any of the data.

I created a new table called tblSavingsDepWith. It will contain all the data for a savings deposit as well as data for a savings withdrawal. It has the following fields (a combination of all fields in the two original tables):

Savings DepWith ID AutoNumber
Savings DepWith Number Number
Savings DepWith Type Short Text "Dep" or "With"
Savings DepWith Date Date/Time
Savings DepWith Amount Currency
Savings DepWith Who Short Text Who made the Dep or With
Savings DepWith Memo Short Text
Savings DepWith Cleared Yes/No
Savings Account Short Text

Originally, I simply renamed the table tblSavingsWithdrawal and edited the fields. I then went through the app and changed all the references to the original two tables. This seemed to confuse access, so I deleted the two original tables and created a new one with all the appropriate fields.

I created the new table in the back-end database, but it doesn't appear under the list of tables in the app. What have I missed?

[Welshgasman]

I would just call it tblSavings. It is a given it is for deposits and withdrawals.
You need to link the new table from the FE.

[ArviLaanemets]

Do you really have all those spaces in your field names? It will be a pain you know where to enter all those brackets when writing scripts or queries!

[willkr]

Another thing I noticed: When I start the app now I get the following message:

The Microsoft Access database engine cannot find the input table or query "tblSavingsWithdraw". Make sure it exists and that its name is spelled correctly.

I have changed all references to tblSavingsWithdraw in the app to read tblSavingsDepWith. I have stepped through the initiation of the start-up form and the error message is generated when I execute the following line of code:
lngSavingsDepWithCount = Nz(DCount("[Savings DepWith Number]", "tblSavingsDepWith"), 0)

Why does the database engine think I am trying to count the entries in tblSavingsWithdraw when it no longer exists.

[Welshgasman]

Have you linked the new table?

[willkr]

I have other savings tables that track things like credits and debits entered as part of the reconciliation process. I also have a table with savings account names and a table for reconciliation results, etc. So it is necessary to have multiple 'Savings" tables.

How do I link the new table from the front end?

[willkr]

OK, I figured out how to link the new table and now the start-up error messages have gone away. Now I have to go through and test all the routines that I changed.

Thanks for your help so-far, guys!

willkr

[willkr]

I've been working through all the routines that access the Savings Credits and Debits, but I have run into a snag.

I'm trying to set a date variable based on the date entered in the form so I have a section of code like this:

'------------------------------------------------------------
' Date_LostFocus
'------------------------------------------------------------
Private Sub Date_LostFocus()
On Error GoTo Date_LostFocus_Err

dteDepDate = Me.[Date]

Date_LostFocus_Exit:
Exit Sub


Date_LostFocus_Err:
MsgBox Error$
Resume Date_LostFocus_Exit


End Sub
'---------------------------------------------

The problem is that this code never gets executed. I have a Date_GotFocus() procedure that works just fine, but I never get to the Date_LostFocus() procedure.

Any suggestions?