Number of Records pulled by Query don't match number of records in table

R

Rebekah

I am trying to figure out why Access does not pull all of
the records that are contained within my database when I
set date parameters. For instance, my database covers the
time period of 1/1/02 through 8/31/04 and has 53623
records. When I run a query with the date parameters of
1/1/02 through 8/31/04 the query only returns 53455
records. I am trying to get bookings values for ranges of
dates and I can not get my numbers generated by the
query/report to match what is in the excel data that I
orginally imported. Any comments or suggestions are
greatly appreciated.

Thank you.
 
R

Rebekah

This is the code behind the query, I have also checked my
date field and there are no blank records

SELECT [SalesTableOrders Complete New].[TRANS DT],
[SalesTableOrders Complete New].[MKT SEG],
[SalesTableOrders Complete New].[ORD VAL],
[SalesTableOrders Complete New].FGC, [SalesTableOrders
Complete New].AGC
FROM [SalesTableOrders Complete New]
WHERE ((([SalesTableOrders Complete New].[TRANS DT])
Between [start date] And [end date]));


Any help is appreciated.

Thank you.
 
T

Tom Ellison

Dear Rebekah:

One thing that might help is to temporarily change the criteria of teh
query query to:

WHERE ((([SalesTableOrders Complete New].[TRANS DT])
NOT Between [start date] And [end date])) OR [TRANS DT] IS NULL;

This may point out some rows that do not have values like what you
thought.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


This is the code behind the query, I have also checked my
date field and there are no blank records

SELECT [SalesTableOrders Complete New].[TRANS DT],
[SalesTableOrders Complete New].[MKT SEG],
[SalesTableOrders Complete New].[ORD VAL],
[SalesTableOrders Complete New].FGC, [SalesTableOrders
Complete New].AGC
FROM [SalesTableOrders Complete New]
WHERE ((([SalesTableOrders Complete New].[TRANS DT])
Between [start date] And [end date]));


Any help is appreciated.

Thank you.
-----Original Message-----
Posting your code would help.
.
Click to expand...
Click to expand...
 
R

Rebekah

I did as you suggested, it pulls all records that have a
transaction date of 8/31/2004.

Do you have any suggestions on how I can get these records
included in my query?

Thank you.
-----Original Message-----
Dear Rebekah:

One thing that might help is to temporarily change the criteria of teh
query query to:

WHERE ((([SalesTableOrders Complete New].[TRANS DT])
NOT Between [start date] And [end date])) OR [TRANS DT] IS NULL;

This may point out some rows that do not have values like what you
thought.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


This is the code behind the query, I have also checked my
date field and there are no blank records

SELECT [SalesTableOrders Complete New].[TRANS DT],
[SalesTableOrders Complete New].[MKT SEG],
[SalesTableOrders Complete New].[ORD VAL],
[SalesTableOrders Complete New].FGC, [SalesTableOrders
Complete New].AGC
FROM [SalesTableOrders Complete New]
WHERE ((([SalesTableOrders Complete New].[TRANS DT])
Between [start date] And [end date]));


Any help is appreciated.

Thank you.
-----Original Message-----
Posting your code would help.
-----Original Message-----
I am trying to figure out why Access does not pull all of
the records that are contained within my database when I
set date parameters. For instance, my database covers
the
time period of 1/1/02 through 8/31/04 and has 53623
records. When I run a query with the date parameters of
1/1/02 through 8/31/04 the query only returns 53455
records. I am trying to get bookings values for ranges
of
dates and I can not get my numbers generated by the
query/report to match what is in the excel data that I
orginally imported. Any comments or suggestions are
greatly appreciated.

Thank you.
.

.
Click to expand...
Click to expand...

.
Click to expand...
 
G

Guest

By chance, what is the format of [TRANS DT] -- is it 'general'? which is the
date & time. I have had an issue that was corrected when I changed the
format to something other than 'general'

SteveD


Rebekah said:
I did as you suggested, it pulls all records that have a
transaction date of 8/31/2004.

Do you have any suggestions on how I can get these records
included in my query?

Thank you.
-----Original Message-----
Dear Rebekah:

One thing that might help is to temporarily change the criteria of teh
query query to:

WHERE ((([SalesTableOrders Complete New].[TRANS DT])
NOT Between [start date] And [end date])) OR [TRANS DT] IS NULL;

This may point out some rows that do not have values like what you
thought.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


This is the code behind the query, I have also checked my
date field and there are no blank records

SELECT [SalesTableOrders Complete New].[TRANS DT],
[SalesTableOrders Complete New].[MKT SEG],
[SalesTableOrders Complete New].[ORD VAL],
[SalesTableOrders Complete New].FGC, [SalesTableOrders
Complete New].AGC
FROM [SalesTableOrders Complete New]
WHERE ((([SalesTableOrders Complete New].[TRANS DT])
Between [start date] And [end date]));


Any help is appreciated.

Thank you.
-----Original Message-----
Posting your code would help.
-----Original Message-----
I am trying to figure out why Access does not pull all
of
the records that are contained within my database when I
set date parameters. For instance, my database covers
the
time period of 1/1/02 through 8/31/04 and has 53623
records. When I run a query with the date parameters of
1/1/02 through 8/31/04 the query only returns 53455
records. I am trying to get bookings values for ranges
of
dates and I can not get my numbers generated by the
query/report to match what is in the excel data that I
orginally imported. Any comments or suggestions are
greatly appreciated.

Thank you.
.

.
Click to expand...

.
Click to expand...
Click to expand...
 
T

Tom Ellison

Dear Rebekah:

YUP!

When you say "BETWEEN #1/1/02# AND #8/31/04#" you are saying something
more specific than you may realize. You are saying "BETWEEN #1/1/02
00:00:00# AND #8/31/04 00:00:00#".

It seems very likely your design has allowed times of day to be
recorded in the [TRANS DT] column. This column does not contain just
dates, but times of day. So, the query is cutting off at the first
moment of the day and omitting all the rest.

Here are a couple of way around it:

BETWEEN #1/1/02# ADN #8/31/04 23:59:59.9#
= #1/1/02#
Click to expand...
AND
< #9/1/04#

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


I did as you suggested, it pulls all records that have a
transaction date of 8/31/2004.

Do you have any suggestions on how I can get these records
included in my query?

Thank you.
-----Original Message-----
Dear Rebekah:

One thing that might help is to temporarily change the criteria of teh
query query to:

WHERE ((([SalesTableOrders Complete New].[TRANS DT])
NOT Between [start date] And [end date])) OR [TRANS DT] IS NULL;

This may point out some rows that do not have values like what you
thought.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


This is the code behind the query, I have also checked my
date field and there are no blank records

SELECT [SalesTableOrders Complete New].[TRANS DT],
[SalesTableOrders Complete New].[MKT SEG],
[SalesTableOrders Complete New].[ORD VAL],
[SalesTableOrders Complete New].FGC, [SalesTableOrders
Complete New].AGC
FROM [SalesTableOrders Complete New]
WHERE ((([SalesTableOrders Complete New].[TRANS DT])
Between [start date] And [end date]));


Any help is appreciated.

Thank you.
-----Original Message-----
Posting your code would help.
-----Original Message-----
I am trying to figure out why Access does not pull all
of
the records that are contained within my database when I
set date parameters. For instance, my database covers
the
time period of 1/1/02 through 8/31/04 and has 53623
records. When I run a query with the date parameters of
1/1/02 through 8/31/04 the query only returns 53455
records. I am trying to get bookings values for ranges
of
dates and I can not get my numbers generated by the
query/report to match what is in the excel data that I
orginally imported. Any comments or suggestions are
greatly appreciated.

Thank you.
.

.
Click to expand...

.
Click to expand...
Click to expand...
 
T

Tom Ellison

Please change ADN to AND.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


Dear Rebekah:

YUP!

When you say "BETWEEN #1/1/02# AND #8/31/04#" you are saying something
more specific than you may realize. You are saying "BETWEEN #1/1/02
00:00:00# AND #8/31/04 00:00:00#".

It seems very likely your design has allowed times of day to be
recorded in the [TRANS DT] column. This column does not contain just
dates, but times of day. So, the query is cutting off at the first
moment of the day and omitting all the rest.

Here are a couple of way around it:

BETWEEN #1/1/02# ADN #8/31/04 23:59:59.9#
= #1/1/02#
Click to expand...
AND
< #9/1/04#

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


I did as you suggested, it pulls all records that have a
transaction date of 8/31/2004.

Do you have any suggestions on how I can get these records
included in my query?

Thank you.
-----Original Message-----
Dear Rebekah:

One thing that might help is to temporarily change the criteria of teh
query query to:

WHERE ((([SalesTableOrders Complete New].[TRANS DT])
NOT Between [start date] And [end date])) OR [TRANS DT] IS NULL;

This may point out some rows that do not have values like what you
thought.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


On Wed, 15 Sep 2004 11:08:47 -0700, "Rebekah"

This is the code behind the query, I have also checked my
date field and there are no blank records

SELECT [SalesTableOrders Complete New].[TRANS DT],
[SalesTableOrders Complete New].[MKT SEG],
[SalesTableOrders Complete New].[ORD VAL],
[SalesTableOrders Complete New].FGC, [SalesTableOrders
Complete New].AGC
FROM [SalesTableOrders Complete New]
WHERE ((([SalesTableOrders Complete New].[TRANS DT])
Between [start date] And [end date]));


Any help is appreciated.

Thank you.
-----Original Message-----
Posting your code would help.
-----Original Message-----
I am trying to figure out why Access does not pull all
of
the records that are contained within my database when I
set date parameters. For instance, my database covers
the
time period of 1/1/02 through 8/31/04 and has 53623
records. When I run a query with the date parameters of
1/1/02 through 8/31/04 the query only returns 53455
records. I am trying to get bookings values for ranges
of
dates and I can not get my numbers generated by the
query/report to match what is in the excel data that I
orginally imported. Any comments or suggestions are
greatly appreciated.

Thank you.
.

.


.
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

running total of records in query 1
Number records in a query 6
display records from a query 1
Update Query for only a certain number of records 1
Query Not Displaying All Main Table Records 1
fixed number of records query 1
Query by number of records 1
query returns records that don't match criteria 5

Top