1 queries for many table

[cikwan82]

Hi every one, how to make i queries for many table

example :

Table 1
name Status
john Ok
mike No
Ann OK

Table 2
Name Status
michel Ok
jony Ok
Bruce No


Queries for OK

Name Satus
John OK
Ann OK
Michel OK
Bruce OK

This is just simple example, actually i have many table Januari until Disember. so when i key in data using form on every month, I want just 1 queries covering all month, so easier to check. Have any idea to solve this?

 

[pr2-eugin]

Idea, here is a good one. Why don't you just combine all the data in one single table so you can have one Query to do what you want?

oneSingleTable

userID    userName    userStatus    userMonthActivity
1        John          Ok            January
2        Mike          No            January
3        Ann           Ok            January
4        Michel        Ok           February
5        Jony          Ok           February
6        Bruce         No           February

Then your Query would be,

SELECT oneSingleTable.userName, oneSingleTable.userStatus, oneSingleTable.userMonthActivity
FROM oneSingleTable
WHERE oneSingleTable.userStatus = 'Ok';

 

[JHB]

You can use a union query.

I don't think you database is set up proper, normally you have only one table with a field in it holding the month number/name, not one table for each month.
I think the Excel "thinking way" with one month per sheet has had influence.

 

[cikwan82]

Thank you Pr2 and JHB, you idea is good, but actually i already make the navigation form for all month with different table,and the data already key in. just simple question is that sql cod can i use for different table?
example:
SELECT JanuaryT.userName, JanuaryT.userStatus, JanuariT.userMonthActivity ,FebruaryT.userName, FebruaryT.userStatus, FebruaryT.userMonthActivity
FROM JanuaryT, February T
WHERE JanuaryT.userStatus, FebruaryT.userStatus = 'Ok';

 

[Galaxiom]

While you continue to have the data in separate tables you will continue to have problems.

The table structure is the important thing to get right in a database.

The data can be moved into the new table structure using queries.

 

[cikwan82]

.. Thank you... my be i should make it separate for every month...

 

[Galaxiom]

No. You should restructure your data.

Currently you are using structure to encode data. You are having problems retrieving that data because it is embedded in the structure. Databases are designed to retrieve data from records.

 

[namliam]

Let me just (quickly) give another voice to give the smae oppionion.

Storing data in time related tables is a bad bad bad idea. Timeperiods / months should be contained in records not in tables.

 

[cikwan82]

i know what you all means, i should have january-disember data only one table, so easier to make query. Now i already have many data from each month for each table, i must copy all data and make just one table and than make query .. it is any idea make all data combine together using button or sql coding?

 

[namliam]

Its possible but its a nightmare... Like JHB said earlier a Union query:

Select * from jan
union all select * from feb
union all select * from mar
union all .... etc. ....

 

[Galaxiom]

Firstly make the new table much like suggested in Post 2. However I would recommend you use a date field for the months and store the month as the first day of the month, on the assumption that you will continue with the database in subsequent years.

Then make an append query something like this and run it:

INSERT INTO newtable(username, userstatus, activitymonth)
SELECT [name], [status], #1/1/2014# FROM januarytable;

Now adjust the query or each month table and run it again until you have done all the months.

BTW. Use mm/dd/yyyy format for the date field data.

 

[cikwan82]

ok i got it.. thank you my friends.. i just make UNION ALL.

 

[Galaxiom]

Now that you have the union all query you can easily convert that to an Insert query and write all the records into one table where they belong.

 

[cikwan82]

Yes.. you are right...