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

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.


--
Rick B



<(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>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
>

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


<(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>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
>

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

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

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

David

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.

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


<(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>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
>

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"));

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.