Combine Tables (1 Viewer)

LSTC018

Registered User.
Local time
Today, 16:50
Joined
Jan 17, 2019
Messages
19
[FONT=&quot]Hi,[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]I have three tables I would like to combine into one new table, the new table will be updated when any of the separate three are changed, the columns I want from each of the tables have the same column headings. Can you help?[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]Cheers[/FONT]
 

Minty

AWF VIP
Local time
Today, 16:50
Joined
Jul 26, 2013
Messages
10,354
If you create the new combined table why do you need to keep using the individual tables?

This seems to be a redundant duplication of data?
 

LSTC018

Registered User.
Local time
Today, 16:50
Joined
Jan 17, 2019
Messages
19
Hi,


The tables have been set up for 6/7 years each relating to specific returned inspection details – the three columns I require ‘Date’ (Date of inspection) – ‘InsType’ (Inspection Type) – Inspector (Inspectors name) If I can Append them and have the new table updated everytime new information comes in I can query the one table for number of inspections per inspector per day and the type of inspection – hope this helps,
[FONT=&quot] [/FONT]
[FONT=&quot]Cheers[/FONT]
 

Gasman

Enthusiastic Amateur
Local time
Today, 16:50
Joined
Sep 21, 2011
Messages
14,044
Why not just use a query?:confused:
 

LSTC018

Registered User.
Local time
Today, 16:50
Joined
Jan 17, 2019
Messages
19
[FONT=&quot]Hi,[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]More than happy to go with whatever is the best way - I'm very new to all this, I'm doing it off my own back, making reporting easier at work (making me look good). I want one table to interrogate. With help from yourselves I now have a form that I can search by dates (between two dates & 7 days from a certain date) I just need one table that I can sort,[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]Cheers[/FONT]
 

Gasman

Enthusiastic Amateur
Local time
Today, 16:50
Joined
Sep 21, 2011
Messages
14,044
I'd look at using a union query to get all three tables into your form.
Then requery if you want to change the sort order/by

I am thinking of read only access though, if it just for reporting.?

That way it is always up to date when you open the form, plus you can query only the fields you are interested in.
 

LSTC018

Registered User.
Local time
Today, 16:50
Joined
Jan 17, 2019
Messages
19
ok - I'll have a go with that - I may be asking for help !!!
 

Gasman

Enthusiastic Amateur
Local time
Today, 16:50
Joined
Sep 21, 2011
Messages
14,044
ok - I'll have a go with that - I may be asking for help !!!

Start in small steps.

Create your form and get the data into it.

Then start to tweak it as you would like or discover you need.
 

LSTC018

Registered User.
Local time
Today, 16:50
Joined
Jan 17, 2019
Messages
19
[FONT=&quot]Hi,[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]Completed my Form - and used a query using UNION ALL and got the 3 table results in to one Table, I have copied the SQL from the form that works fine, and it is looking at a Table it’s now looking at a Query table (not sure if that's the correct terminology) and it doesn't work. I’ve highlighted in RED the only thing I've changed, will this SQL look at a query?[/FONT]
[FONT=&quot]
[/FONT]
[FONT=&quot]Cheers
[/FONT]




Option Compare Database
Private Sub Command12_Click()
' search button
Call search
End Sub
Sub search()
Dim strCriteria, task As String

Me.Refresh
If IsNull(Me.OrderDateFrom) Or IsNull(Me.OrderDateTo) Then
MsgBox "Please enter the date range", vbInformation, "Date Range Required"
Me.OrderDateFrom.SetFocus

Else
strCriteria = "([InspDate] between #" & Format(Me.OrderDateFrom, "mm/dd/yyyy") & "# And #" & Format(Me.OrderDateTo, "mm/dd/yyyy") & "#)"
task = "Select * from ENW - UploadResults where (" & strCriteria & ") order by [InspDate]"
DoCmd.ApplyFilter task

End If

End Sub
 

Gasman

Enthusiastic Amateur
Local time
Today, 16:50
Joined
Sep 21, 2011
Messages
14,044
Yes, however you have made a common mistake in your declaration of variables.

*EVERY* variable should be declared with it's type. strCriteria I believe will be a variant, but does not really matter in this instance. You should have

Code:
Dim strCriteria as String, task as String

I believe if you have to have spaces in query names then surround with []

[code]
task = "Select * from [ENW - UploadResults] where (" & strCriteria & ") order by [InspDate]"
 

LSTC018

Registered User.
Local time
Today, 16:50
Joined
Jan 17, 2019
Messages
19
Hi Gasman,


Good feeling when it works - Thanks again for your support and advice. I'm sure I'll be back,


Cheers
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 11:50
Joined
Feb 28, 2001
Messages
26,999
Give you one more bit of advice... in general, it is not a good idea to have complex table names - in the sense of having embedded spaces or punctuation as part of the name. Tables work OK with underscores in the name and I believe it is POSSIBLE to have a dollar sign in a name. Other things like dashes, slashes, hash-tags (octothorpes), and percent signs are generally not good. If you get table names down to letters, digits, and perhaps the underscore to provide a word-break, you can omit the brackets [] around the name. This is also true for complex field names.
 

Users who are viewing this thread

Top Bottom