Do I have to have equal number of records in each table to combine them?

[david.isaacks]

I have two tables. One for Feb and one for Mar. The fields are
identiical in each table however the records are not equal in number.
For instance one table Output_MAR_2006 has 107,000 records and
Output_FEB_2006 has 48,000 records. For one field in each table
(Facility VISN) I want to combine to run a report to find out the
trend. The field is a number field, and in the report I need to count a
certian number of records which equal (22) and compare it against FEB
records which equal (22). So I thought it I just add both tables into
one and only select the (Facility VISN) field I could do the math in
the report. However, when I combine them, the the data gets all messed
up. Plus I can never get the report to open. It's like both colums have
to be the same lenght.

David

 

[Rick B]

You need to combine these records into one table (with a date field) by
running an APPEND query. You should not store data in different tables when
the data is the same, but each table represents a different subset of the
data. Instead, add a field to differentiate the subsets.

In your example, this should all be in one table and you can do that by
running an APPEND query.

 

[Tom Ellison]

Dear David:

There are many fine details in doing this. I cannot work it out for you
without some help.

What I suggest is that you provide two queries, one for each month, that
produce the results you want to combine. I'll try to work them together
appropriately.

Sound good to you?

Tom Ellison

 

[david.isaacks]

The data is exported from a web application CRM in a delimited file.
When I import it into access it places the date and time into a text
field. It will not allow me to import that field in as a date field
since the time is with it. This is the format the date and time come
into access as (2/21/2006 6:07:52 AM) I can query by date in the CRM
application and that is how I can exporting by month. I can export
everything record at once, but then I cannot sort by month once I get
it into access due to the way the CRM time stamps the records. If I can
change this text field date into a data field please let me know. I
don't really need the time in access. The time is only used in the CRM
application because it is a management system for a call center.

David

 

[david.isaacks]

I have two queries. One "FP Only FEB 2006" and the other "FP FEB Only
MAR 2006". Each query holds a field called (Facility VISN) the VISN is
a number that repsresents a Veterans Intergrated Service Network. So
for instance I have 14 VISN and a Null VISN. VISN numbers are 5, 6, 7,
10, 12, 15, 16, 17, 18, 19, 20, 21, 22, 23 and then I also want to be
able to compare the Null records assocated with this field. So for "FP
Only FEB 2006" I want to compare the VISN number 5 against "FP Only MAR
2006" VISN number 5. To find the difference, percentage in growth,
trend, etc... So for instance the call volume in Feb for VISN 5 was 800
and the call voluime in VISN 5 for MAR was 1200.

What other info do you need?

David

 

[david.isaacks]

The fields are the same, the data is of course different depending on
the input.

David

 

[david.isaacks]

Ok, I don't know how I just did it, but I just was able to convert my
dates and times in a text field over to a date field. (General Date
format) I stand corrected. I was sure I could not do this before.

 

[Tom Ellison]

Dear David:

I'd like to see the queries. There is a text version of any query called
the SQL View. This is ideal for interchange in a newsgroup because it
conveys things very specifically and can be posted.

Tom Ellison

 

[david.isaacks]

March 2006

SELECT Output_MAR_2006.ID, Output_MAR_2006.New, Output_MAR_2006.[SR #],
Output_MAR_2006.Type, Output_MAR_2006.Area, Output_MAR_2006.[Sub Area],
Output_MAR_2006.Status, Output_MAR_2006.Substatus,
Output_MAR_2006.[Last Name & SSN], Output_MAR_2006.[Last Name],
Output_MAR_2006.[First Name], Output_MAR_2006.[Social Security #],
Output_MAR_2006.Facility, Output_MAR_2006.Owner,
Output_MAR_2006.Source, Output_MAR_2006.Resolution,
Output_MAR_2006.Summary, Output_MAR_2006.[Billing Station],
Output_MAR_2006.[Facility VISN], Output_MAR_2006.Opened,
Output_MAR_2006.Closed, Output_MAR_2006.[Date Assigned],
Output_MAR_2006.[Created By], Output_MAR_2006.[Date Modified],
Output_MAR_2006.[First Closed Date], Output_MAR_2006.Organization
FROM Output_MAR_2006;

Query from the one above to get only records that have the Type of FP
(Below is the one I am trying to combine with the other one that is
below for Feb.) This one has 107,217 records.

SELECT *
FROM [Output_MAR_2006 Query]
WHERE ((([Output_MAR_2006 Query].Type)="FP"));

Febraury 2006

SELECT Output_FEB_2006.ID, Output_FEB_2006.New, Output_FEB_2006.[SR #],
Output_FEB_2006.Type, Output_FEB_2006.Area, Output_FEB_2006.[Sub Area],
Output_FEB_2006.Status, Output_FEB_2006.Substatus,
Output_FEB_2006.[Last Name & SSN], Output_FEB_2006.[Last Name],
Output_FEB_2006.[First Name], Output_FEB_2006.[Social Security #],
Output_FEB_2006.Facility, Output_FEB_2006.Owner,
Output_FEB_2006.Source, Output_FEB_2006.Resolution,
Output_FEB_2006.Summary, Output_FEB_2006.[Billing Station],
Output_FEB_2006.[Facility VISN], Output_FEB_2006.Opened,
Output_FEB_2006.Closed, Output_FEB_2006.[Date Assigned],
Output_FEB_2006.[Created By], Output_FEB_2006.[Date Modified],
Output_FEB_2006.[First Closed Date], Output_FEB_2006.Organization
FROM Output_FEB_2006;


Query from the one above to get only records that have the Type of FP.
This one has 20,408 records.

SELECT *
FROM [Output_FEB_2006 Query]
WHERE ((([Output_FEB_2006 Query].Type)="FP"));

 

[david.isaacks]

Take that back, I can only do this for the Feb one, the march one is
too large and it will not let me change it from a txt to data field.
Saays not enought disk space.

 

[david.isaacks]

March 2006

SELECT Output_MAR_2006.ID, Output_MAR_2006.New, Output_MAR_2006.[SR #],
Output_MAR_2006.Type, Output_MAR_2006.Area, Output_MAR_2006.[Sub Area],
Output_MAR_2006.Status, Output_MAR_2006.Substatus,
Output_MAR_2006.[Last Name & SSN], Output_MAR_2006.[Last Name],
Output_MAR_2006.[First Name], Output_MAR_2006.[Social Security #],
Output_MAR_2006.Facility, Output_MAR_2006.Owner,
Output_MAR_2006.Source, Output_MAR_2006.Resolution,
Output_MAR_2006.Summary, Output_MAR_2006.[Billing Station],
Output_MAR_2006.[Facility VISN], Output_MAR_2006.Opened,
Output_MAR_2006.Closed, Output_MAR_2006.[Date Assigned],
Output_MAR_2006.[Created By], Output_MAR_2006.[Date Modified],
Output_MAR_2006.[First Closed Date], Output_MAR_2006.Organization
FROM Output_MAR_2006;



SELECT *
FROM [Output_MAR_2006 Query]
WHERE ((([Output_MAR_2006 Query].Type)="FP"));

Febraury 2006

SELECT Output_FEB_2006.ID, Output_FEB_2006.New, Output_FEB_2006.[SR #],
Output_FEB_2006.Type, Output_FEB_2006.Area, Output_FEB_2006.[Sub Area],
Output_FEB_2006.Status, Output_FEB_2006.Substatus,
Output_FEB_2006.[Last Name & SSN], Output_FEB_2006.[Last Name],
Output_FEB_2006.[First Name], Output_FEB_2006.[Social Security #],
Output_FEB_2006.Facility, Output_FEB_2006.Owner,
Output_FEB_2006.Source, Output_FEB_2006.Resolution,
Output_FEB_2006.Summary, Output_FEB_2006.[Billing Station],
Output_FEB_2006.[Facility VISN], Output_FEB_2006.Opened,
Output_FEB_2006.Closed, Output_FEB_2006.[Date Assigned],
Output_FEB_2006.[Created By], Output_FEB_2006.[Date Modified],
Output_FEB_2006.[First Closed Date], Output_FEB_2006.Organization
FROM Output_FEB_2006;




SELECT *
FROM [Output_FEB_2006 Query]
WHERE ((([Output_FEB_2006 Query].Type)="FP"));

 

[Tom Ellison]

Dear David:

I reproduce your queries here, simplified and formatted to my preferences:

SELECT ID, New, [SR #], Type, Area, [Sub Area],
Status, Substatus, [Last Name & SSN], [Last Name],
[First Name], [Social Security #], Facility, Owner,
Source, Resolution, Summary, [Billing Station],
[Facility VISN], Opened, Closed, [Date Assigned],
[Created By], [Date Modified], [First Closed Date], Organization
FROM Output_MAR_2006;

SELECT ID, New, [SR #], Type, Area, [Sub Area],
Status, Substatus, [Last Name & SSN], [Last Name],
[First Name], [Social Security #], Facility, Owner,
Source, Resolution, Summary, [Billing Station],
[Facility VISN], Opened, Closed, [Date Assigned],
[Created By], [Date Modified], [First Closed Date], Organization
FROM Output_FEB_2006;

You said:

"For one field in each table (Facility VISN) I want to combine to run a
report to find out the trend. The field is a number field, and in the report
I need to count a certian number of records which equal (22) and compare it
against FEB records which equal (22)."

What does it mean "count a certain number of records which equal (22)"?
Please explain and give an example.

Tom Ellison

March 2006

SELECT Output_MAR_2006.ID, Output_MAR_2006.New, Output_MAR_2006.[SR #],
Output_MAR_2006.Type, Output_MAR_2006.Area, Output_MAR_2006.[Sub Area],
Output_MAR_2006.Status, Output_MAR_2006.Substatus,
Output_MAR_2006.[Last Name & SSN], Output_MAR_2006.[Last Name],
Output_MAR_2006.[First Name], Output_MAR_2006.[Social Security #],
Output_MAR_2006.Facility, Output_MAR_2006.Owner,
Output_MAR_2006.Source, Output_MAR_2006.Resolution,
Output_MAR_2006.Summary, Output_MAR_2006.[Billing Station],
Output_MAR_2006.[Facility VISN], Output_MAR_2006.Opened,
Output_MAR_2006.Closed, Output_MAR_2006.[Date Assigned],
Output_MAR_2006.[Created By], Output_MAR_2006.[Date Modified],
Output_MAR_2006.[First Closed Date], Output_MAR_2006.Organization
FROM Output_MAR_2006;

Query from the one above to get only records that have the Type of FP
(Below is the one I am trying to combine with the other one that is
below for Feb.) This one has 107,217 records.

SELECT *
FROM [Output_MAR_2006 Query]
WHERE ((([Output_MAR_2006 Query].Type)="FP"));

Febraury 2006

SELECT Output_FEB_2006.ID, Output_FEB_2006.New, Output_FEB_2006.[SR #],
Output_FEB_2006.Type, Output_FEB_2006.Area, Output_FEB_2006.[Sub Area],
Output_FEB_2006.Status, Output_FEB_2006.Substatus,
Output_FEB_2006.[Last Name & SSN], Output_FEB_2006.[Last Name],
Output_FEB_2006.[First Name], Output_FEB_2006.[Social Security #],
Output_FEB_2006.Facility, Output_FEB_2006.Owner,
Output_FEB_2006.Source, Output_FEB_2006.Resolution,
Output_FEB_2006.Summary, Output_FEB_2006.[Billing Station],
Output_FEB_2006.[Facility VISN], Output_FEB_2006.Opened,
Output_FEB_2006.Closed, Output_FEB_2006.[Date Assigned],
Output_FEB_2006.[Created By], Output_FEB_2006.[Date Modified],
Output_FEB_2006.[First Closed Date], Output_FEB_2006.Organization
FROM Output_FEB_2006;


Query from the one above to get only records that have the Type of FP.
This one has 20,408 records.

SELECT *
FROM [Output_FEB_2006 Query]
WHERE ((([Output_FEB_2006 Query].Type)="FP"));

 

[david.isaacks]

Tom,

The 22 is just a number idetifying a VISN. So for that record 22 was
enter to associate that VISN with that record.

Feb 2006 Mar 2006
[Facility VISN] [Facility VISN]

22 22
22 22
22 22
22 22
22 22
22 22
22
22
22
22

Total 6 Total 10

An increase of 4
% increase ...


Then once April is over I would add another table (APR 2006) and want
to add it in to find the trend.

Thanks,
David

 

[Tom Ellison]

Dear David:

SELECT ID, New, [SR #], Type, Area, [Sub Area],
Status, Substatus, [Last Name & SSN], [Last Name],
[First Name], [Social Security #], Facility, Owner,
Source, Resolution, Summary, [Billing Station],
[Facility VISN], Opened, Closed, [Date Assigned],
[Created By], [Date Modified], [First Closed Date], Organization
FROM Output_MAR_2006;

SELECT ID, New, [SR #], Type, Area, [Sub Area],
Status, Substatus, [Last Name & SSN], [Last Name],
[First Name], [Social Security #], Facility, Owner,
Source, Resolution, Summary, [Billing Station],
[Facility VISN], Opened, Closed, [Date Assigned],
[Created By], [Date Modified], [First Closed Date], Organization
FROM Output_FEB_2006;

How about:

SELECT [Facility VISN], MoNo, Count(*) AS Ct
FROM (
SELECT 3 AS MoNo, [Facility VISN] FROM Output_MAR_2006
UNION ALL
SELECT 2 AS MoNo, [Facility VISN] FROM Output_FEB_2006
) X
GROUP BY [Facility VISN], MoNo
ORDER BY [Facility VISN], MoNo

Tom Ellison

 

[david.isaacks]

This is what I got, It looks like it will work. Thanks alot. I will
have to work on getting this into a report tomorrow.
David

Facility VISN MoNo Ct
2 22166
3 70847
0 2 75
0 3 220
10 2 932
10 3 6043
12 2 2101
12 3 8096
15 2 2022
15 3 6638
16 2 2692
16 3 13803
17 2 883
17 3 6114
18 2 1006
18 3 6264
19 2 826
19 3 5085
20 2 572
20 3 5222
21 2 21
21 3 7528
22 2 1764
22 3 7160
23 2 387
23 3 11587
5 2 1378
5 3 4255
6 2 2366
6 3 8889
7 2 3037
7 3 8914