Acess query

A

Aikiart

Hello all,

I'm trying to do a query on 2 tables. This query works as long as I create
a table to hold one day of data.
The query is designed to show names of users that were not in the table with
the date. For example, I want to see all Gateway user id, that were not in
8-02-08. I'm able to do this query.

What i'd like to do is import several days of data into a table and create a
query that would allow a result set just for one day. The problem I
encounter is that my query, is using the entire data set to determine the
result, rather than a specific day. Does anyone know of a way to do a query
on several days worth of data but that is only looking at one day at a time?

The flag is a criteria for eliminating weekend employees from the result.

Thank you
Art

SELECT [Gateway users].[Gateway user id]
FROM [Gateway users] LEFT JOIN [8-02-08] ON [Gateway users].[Gateway user
id] = [8-02-08].[Login Name]
WHERE ((([8-02-08].[Login Name]) Is Null) AND (([Gateway users].flag)<>Yes));
 
K

KARL DEWEY

Your problem is the many tables where you should have one. You should not
have a table per day but one with a field (DateTime datatype) for the date.

To get there use a union query like this --
SELECT #8/2/08# AS DataDate, [8-02-08].[Login Name] AS [Login Name]
FROM [8-02-08]
UNION ALL SELECT #8/3/08# AS DataDate, [8-03-08].[Login Name] AS [Login Name]
FROM [8-03-08]
UNION ALL SELECT #8/4/08# AS DataDate, [8-04-08].[Login Name] AS [Login Name]
FROM [8-04-08]
UNION ALL SELECT #8/5/08# AS DataDate, [8-05-08].[Login Name] AS [Login Name]
FROM [8-05-08];
 
A

Aikiart

Thanks Karl,

I probably wasn't very clear about what I was trying to do. I'm able to
import data for all dates into one table, call the table [AllDays], the
problem i'm encountering is when I do a left join on the [AllDays] table with
the [Gateway.user] table i'm not able to get a result set of users that did
not login for a given day. The tables are as follows.

[Gateway.user] all users of the domain
Fields:

username
user id

[AllDays]
Fields:

username
date of login
user id

I do the left join which results in records being shown that are not in
[Gateway.user] but only when i just have one days worth of data. When I use
more than one day, i'm getting a result set that uses all the data to
determine whether a user logged in. I'd like to have all the days present
and based on which day I query, I would get a result set of the users that
did not log in that day.

Hope this clears up what I'm trying to do.

Thanks for your and anyones help

Art

KARL DEWEY said:
Your problem is the many tables where you should have one. You should not
have a table per day but one with a field (DateTime datatype) for the date.

To get there use a union query like this --
SELECT #8/2/08# AS DataDate, [8-02-08].[Login Name] AS [Login Name]
FROM [8-02-08]
UNION ALL SELECT #8/3/08# AS DataDate, [8-03-08].[Login Name] AS [Login Name]
FROM [8-03-08]
UNION ALL SELECT #8/4/08# AS DataDate, [8-04-08].[Login Name] AS [Login Name]
FROM [8-04-08]
UNION ALL SELECT #8/5/08# AS DataDate, [8-05-08].[Login Name] AS [Login Name]
FROM [8-05-08];

--
KARL DEWEY
Build a little - Test a little


Aikiart said:
Hello all,

I'm trying to do a query on 2 tables. This query works as long as I create
a table to hold one day of data.
The query is designed to show names of users that were not in the table with
the date. For example, I want to see all Gateway user id, that were not in
8-02-08. I'm able to do this query.

What i'd like to do is import several days of data into a table and create a
query that would allow a result set just for one day. The problem I
encounter is that my query, is using the entire data set to determine the
result, rather than a specific day. Does anyone know of a way to do a query
on several days worth of data but that is only looking at one day at a time?

The flag is a criteria for eliminating weekend employees from the result.

Thank you
Art

SELECT [Gateway users].[Gateway user id]
FROM [Gateway users] LEFT JOIN [8-02-08] ON [Gateway users].[Gateway user
id] = [8-02-08].[Login Name]
WHERE ((([8-02-08].[Login Name]) Is Null) AND (([Gateway users].flag)<>Yes));
Click to expand...
Click to expand...
 
K

KARL DEWEY

Use two queries, the first gives a list of all possibilities, the second find
who did not login.
Aikiart_1 --
SELECT Gateway_user.username, Gateway_user.[user id], AllDays.[date of login]
FROM AllDays, Gateway_user
GROUP BY Gateway_user.username, Gateway_user.[user id], AllDays.[date of
login];

SELECT Aikiart_1.username, Aikiart_1.[user id], Aikiart_1.[date of login]
FROM Aikiart_1 LEFT JOIN AllDays ON (Aikiart_1.[date of login] =
AllDays.[date of login]) AND (Aikiart_1.[user id] = AllDays.[user id])
WHERE (((AllDays.[user id]) Is Null) AND ((AllDays.[date of login]) Is Null));

--
KARL DEWEY
Build a little - Test a little


Aikiart said:
Thanks Karl,

I probably wasn't very clear about what I was trying to do. I'm able to
import data for all dates into one table, call the table [AllDays], the
problem i'm encountering is when I do a left join on the [AllDays] table with
the [Gateway.user] table i'm not able to get a result set of users that did
not login for a given day. The tables are as follows.

[Gateway.user] all users of the domain
Fields:

username
user id

[AllDays]
Fields:

username
date of login
user id

I do the left join which results in records being shown that are not in
[Gateway.user] but only when i just have one days worth of data. When I use
more than one day, i'm getting a result set that uses all the data to
determine whether a user logged in. I'd like to have all the days present
and based on which day I query, I would get a result set of the users that
did not log in that day.

Hope this clears up what I'm trying to do.

Thanks for your and anyones help

Art

KARL DEWEY said:
Your problem is the many tables where you should have one. You should not
have a table per day but one with a field (DateTime datatype) for the date.

To get there use a union query like this --
SELECT #8/2/08# AS DataDate, [8-02-08].[Login Name] AS [Login Name]
FROM [8-02-08]
UNION ALL SELECT #8/3/08# AS DataDate, [8-03-08].[Login Name] AS [Login Name]
FROM [8-03-08]
UNION ALL SELECT #8/4/08# AS DataDate, [8-04-08].[Login Name] AS [Login Name]
FROM [8-04-08]
UNION ALL SELECT #8/5/08# AS DataDate, [8-05-08].[Login Name] AS [Login Name]
FROM [8-05-08];

--
KARL DEWEY
Build a little - Test a little


Aikiart said:
Hello all,

I'm trying to do a query on 2 tables. This query works as long as I create
a table to hold one day of data.
The query is designed to show names of users that were not in the table with
the date. For example, I want to see all Gateway user id, that were not in
8-02-08. I'm able to do this query.

What i'd like to do is import several days of data into a table and create a
query that would allow a result set just for one day. The problem I
encounter is that my query, is using the entire data set to determine the
result, rather than a specific day. Does anyone know of a way to do a query
on several days worth of data but that is only looking at one day at a time?

The flag is a criteria for eliminating weekend employees from the result.

Thank you
Art

SELECT [Gateway users].[Gateway user id]
FROM [Gateway users] LEFT JOIN [8-02-08] ON [Gateway users].[Gateway user
id] = [8-02-08].[Login Name]
WHERE ((([8-02-08].[Login Name]) Is Null) AND (([Gateway users].flag)<>Yes));
Click to expand...
Click to expand...
Click to expand...
 
A

Aikiart

Thanks Karl, that worked!!!

Art

KARL DEWEY said:
Use two queries, the first gives a list of all possibilities, the second find
who did not login.
Aikiart_1 --
SELECT Gateway_user.username, Gateway_user.[user id], AllDays.[date of login]
FROM AllDays, Gateway_user
GROUP BY Gateway_user.username, Gateway_user.[user id], AllDays.[date of
login];

SELECT Aikiart_1.username, Aikiart_1.[user id], Aikiart_1.[date of login]
FROM Aikiart_1 LEFT JOIN AllDays ON (Aikiart_1.[date of login] =
AllDays.[date of login]) AND (Aikiart_1.[user id] = AllDays.[user id])
WHERE (((AllDays.[user id]) Is Null) AND ((AllDays.[date of login]) Is Null));

--
KARL DEWEY
Build a little - Test a little


Aikiart said:
Thanks Karl,

I probably wasn't very clear about what I was trying to do. I'm able to
import data for all dates into one table, call the table [AllDays], the
problem i'm encountering is when I do a left join on the [AllDays] table with
the [Gateway.user] table i'm not able to get a result set of users that did
not login for a given day. The tables are as follows.

[Gateway.user] all users of the domain
Fields:

username
user id

[AllDays]
Fields:

username
date of login
user id

I do the left join which results in records being shown that are not in
[Gateway.user] but only when i just have one days worth of data. When I use
more than one day, i'm getting a result set that uses all the data to
determine whether a user logged in. I'd like to have all the days present
and based on which day I query, I would get a result set of the users that
did not log in that day.

Hope this clears up what I'm trying to do.

Thanks for your and anyones help

Art

KARL DEWEY said:
Your problem is the many tables where you should have one. You should not
have a table per day but one with a field (DateTime datatype) for the date.

To get there use a union query like this --
SELECT #8/2/08# AS DataDate, [8-02-08].[Login Name] AS [Login Name]
FROM [8-02-08]
UNION ALL SELECT #8/3/08# AS DataDate, [8-03-08].[Login Name] AS [Login Name]
FROM [8-03-08]
UNION ALL SELECT #8/4/08# AS DataDate, [8-04-08].[Login Name] AS [Login Name]
FROM [8-04-08]
UNION ALL SELECT #8/5/08# AS DataDate, [8-05-08].[Login Name] AS [Login Name]
FROM [8-05-08];

--
KARL DEWEY
Build a little - Test a little


:

Hello all,

I'm trying to do a query on 2 tables. This query works as long as I create
a table to hold one day of data.
The query is designed to show names of users that were not in the table with
the date. For example, I want to see all Gateway user id, that were not in
8-02-08. I'm able to do this query.

What i'd like to do is import several days of data into a table and create a
query that would allow a result set just for one day. The problem I
encounter is that my query, is using the entire data set to determine the
result, rather than a specific day. Does anyone know of a way to do a query
on several days worth of data but that is only looking at one day at a time?

The flag is a criteria for eliminating weekend employees from the result.

Thank you
Art

SELECT [Gateway users].[Gateway user id]
FROM [Gateway users] LEFT JOIN [8-02-08] ON [Gateway users].[Gateway user
id] = [8-02-08].[Login Name]
WHERE ((([8-02-08].[Login Name]) Is Null) AND (([Gateway users].flag)<>Yes));
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

Dates In Excel 5
Append Crosstab Query to Existing Table 0
Incorporating the User Profile location into a document template path 1
Programming reporting sequences into project programmes 0
Report Group Footer DLookUp 0
bullets numbering- and series of dots above and below the list 1
Turning 1 line into 2 columns 2
Customise default Flag categories 0

Top