I want to create 3 queries from 3 tables

[btamsgn]

Hi Everyone,

I want to create 3 query based on 3 tables
3 query like images in the attachment

Looking forward to receiving from your reply.
Thank you

 

[June7]

How did you produce those images - Excel?

Why are you trying to link on GT fields? Can't link 5 fields from one table to same field of another table. Would have to pull Tb2 into query 5 times. Link one GTx field from each copy of Tb2 to GT field in Tb1.

I can't make sense out of this data. There are no apparent relationships. How do you expect to associate Da table with the other tables?

 

[btamsgn]

1. Yes, I did data table by excel.
2. Now, I want to make the data table by query in microsoft access
You can design or organize table to make query in access ?

 

[GPGeorge]

One of the better ways to get useful help is to explain as much as possible what the purpose is and why you need to manipulate data like this. Another VERY helpful thing is to explain abbreviations and acronyms so that is not a barrier to communication. It's hard enough to grasp the context, but when looking at coded names like these, an added level of ambiguity interferes.

Also, these "tables" are pretty much Excel worksheets anyway, not properly designed relational database tables, so any attempt to work with them in this format is counter-productive. The data needs to be normalized in relational tables to be used in a relational database.

To that end, please start by explaining what is going on, what the data is all about and why you need these queries. We'll want to correct the table design, of course, but first we need to understand what the data represents.

 

[The_Doc_Man]

May I respectfully suggest that in order to do anything significant with these tables as you have displayed them, you need to normalize them. They are clearly NOT normalized. What you have in Excel may indeed make perfect sense, but the problem is that Access and Excel see data entirely differently. Access is FAR more efficient in organizing and linking things. Excel? Not so much.

If you choose to normalize these tables, may I direct you to two sources?

In this forum, which IS primarily a database forum, use the search function (upper right, at the end of the menu bar) to search for NORMALIZATION. Access and, for that matter, any other SQL-based database tool, works FAR better when dealing with normalized data. It is possible but usually insanely difficult to work with extensive non-normalized data and the efficiency of the Access database engine (called ACE) is not so good with such data.

In the general web, you would search for DATABASE NORMALIZATION because the word "normalization" appears in math, chemistry, international diplomacy, certain medical applications, and maybe a couple of other topics. IF you search the web this way, start with articles that originate from the .EDU domain, usually schools and colleges. Not that .COM sites aren't good, but they frequently have something to sell you and it might be distracting. Once you have read a few articles, you can go into the .COM domain articles with more confidence because by then you will be able to figure out what they are selling, whether products OR services OR training.

 

[btamsgn]

1. I'm not good at English so that I can't express my ideas and I have to use image to specify what I'd like to do.

2. I think only 2 tables: 1. "Tb1" table is unique value "GT". I can combine MaID and MaH into "Tb1" table and the other table "Tb2"

3. "data" table has 5 columns such as date, C1,C2,C3 and C4 ( value of C1,C2,C3,C4 from "GT" column in "Tb1" table). Staff and employee typed everyday.

4. I want to make report daily from "data" table with query because access make a report better than excel.

5. Access can multiply with much value from one row.

Pls find file in the attachment for your reference.

 

[June7]

Book2.zip cannot open - error "invalid".

 

[SHANEMAC51]

I want to create 3 query based on 3 tables

Query Q_tb1

SELECT Tb1.MaID, 1 as ztyp,Da.Ngay, Tb1.GT AS A1
FROM Tb1 INNER JOIN Da ON Tb1.GT = Da.C1

UNION SELECT Tb1.MaID,2, Da.Ngay, Tb1.GT
FROM Tb1 INNER JOIN Da ON Tb1.GT = Da.C2

UNION SELECT Tb1.MaID, 3,Da.Ngay, Tb1.GT
FROM Tb1 INNER JOIN Da ON Tb1.GT = Da.C3

UNION SELECT Tb1.MaID,4, Da.Ngay, Tb1.GT
FROM Tb1 INNER JOIN Da ON Tb1.GT = Da.C4

ORDER BY 1, 2, 3;

Query Q_tb1p
I compressed the date to a year and the values of 1,0000000000002 to integers for compactness and clarity

otherwise, you need to remove integer division by 10 and multiplication by 10

TRANSFORM Count(Q_tb1.ztyp) AS [Count-ztyp2]
SELECT Format(Q_tb1.[Ngay],"yyyy") AS wyyyy, (Q_tb1.[A1]\10)*10 AS w2,
Count(Q_tb1.ztyp) AS [count ztyp1]
FROM Q_tb1
GROUP BY Format(Q_tb1.[Ngay],"yyyy"), (Q_tb1.[A1]\10)*10
PIVOT Q_tb1.MaID;

wyyyy	w2	count ztyp1	AC1	AD3	AL4	BC2	LC5
2021	0	111	80	3	3	13	12
2021	10	117		5	9	55	48
2021	20	90		30	14		46
2021	30	42			42

 

[SHANEMAC51]

For example: Date 10/3/2021 with 4 values however query with 3 values, etc.
Date 12/3/2021 with 4 values however query with 2 values, etc.

it depends on the conditions of merging tables

ON Tb1.GT = Da.C1
ON Tb1.GT = Da.C2
ON Tb1.GT = Da.C3
ON Tb1.GT = Da.C4

 

[SHANEMAC51]

1. Yes, I did data table by excel.
2. Now, I want to make the data table by query in microsoft access
You can design or organize table to make query in access ?

these are problems due to the DOUBLE data type
, they are visually the same, but not equal in reality (a form for comparing 2 tables)

 

[btamsgn]

yo

Book2.zip cannot open - error "invalid".

you use winrar or winzip soft to open it

 

[btamsgn]

these are problems due to the DOUBLE data type
, they are visually the same, but not equal in reality (a form for comparing 2 tables)

Dear SHANEMAC51,
I find error, and I changed GT number into "short text" and C1,C2,C3,C4 number into "short text" => results are Ok

 

[Uncle Gizmo]

Another VERY helpful thing is to explain abbreviations and acronyms so that is not a barrier to communication.

Yes, take note @isladogs !!!

 

[Uncle Gizmo]

1. I'm not good at English

Use Google translate.

First, explain in your mother tongue, convert to English.

Now convert the English translation back to your language.

Does it make sense?

If not, adjust to suit by replacing complicated ambitious statements with simple statements, which are easier for Google to translate....

 

[June7]

Well, I don't have Winzip or Winrar (and I won't install), only Windows Compression.

Seem to have good assistance now so will let them carry on.

 

[isladogs]

Yes, take note @isladogs !!!

DWYM UG

 

[isladogs]

Wait .... OIC