iT邦幫忙

1

[重發] Access 的SQL查詢問題

前面發的問題可能不清楚Access 的SQL查詢問題
我再重新說明一次好了,
下面區塊是Excel表格,我要在Access的資料表中做查詢,
現在Access內已建立S、M、P三個table,欄位名稱同表單名稱,
tableP有P、SP兩個欄,SP是S乘P的數值;項次用於說明。

問題就是,可以在一次查詢中,查出下面兩個查詢嗎?
該怎麼用SQL寫,或是用VBA程式碼去撈資料。

查詢1: X欄
count(tableM小於120.9825的數值)=X項次1
count(tableM小於122.3975的數值)=X項次2
...
count(tableM小於142.2075的數值)=X項次16
...
count(tableM小於163.4325的數值)=X項次30
count(tableM大於等於163.4325的數值)=X項次31=0

查詢2: C欄
除了C1與C31外,都是X-(前項X)的值
C項次1=X項次1=0
C項次2=X項次2-X項次1=0,C項次3=X項次3-X項次2=0
...
C項次16=X項次16-X項次15=30
...
C項次30=X項次30-X項次29=0,C項次31=X項次31=0

S	   項次  M	P       SP	        X      C
141.5      1	141.68	85.5%	120.9825	0	0 
	   2	141.75	86.5%	122.3975	0	0 
	   3	141.64	87.5%	123.8125	0	0 
	   4	141.77	88.5%	125.2275	0	0 
	   5	141.72	89.5%	126.6425	0	0 
	   6	141.7	90.5%	128.0575	0	0 
	   7	141.75	91.5%	129.4725	0	0 
           8	141.76	92.5%	130.8875	0	0 
   	   9	141.72	93.5%	132.3025	0	0 
	   10	141.75	94.5%	133.7175	0	0 
	   11	141.78	95.5%	135.1325	0	0 
	   12	141.7	96.5%	136.5475	0	0 
	   13	141.72	97.5%	137.9625	0	0 
	   14	141.75	98.5%	139.3775	0	0 
	   15	141.76	99.5%	140.7925	0	0 
	   16	141.65	100.5%	142.2075	30	30
	   17	141.68	101.5%	143.6225	30	0
	   18	141.68	102.5%	145.0375	30	0 
	   19	141.72	103.5%	146.4525	30	0 
	   20	141.75	104.5%	147.8675	30	0 
	   21	141.78	105.5%	149.2825	30	0 
	   22	141.75	106.5%	150.6975	30	0 
	   23	141.74	107.5%	152.1125	30	0 
	   24	141.75	108.5%	153.5275	30	0 
	   25	141.76	109.5%	154.9425	30	0 
	   26	141.75	110.5%	156.3575	30	0 
	   27	141.72	111.5%	157.7725	30	0 
	   28	141.78	112.5%	159.1875	30	0 
	   29	141.76	113.5%	160.6025	30	0 
	   30	141.75	114.5%	162.0175	30	0 
	   31	        115.5%	163.4325	0	0 

在看不懂我就放棄了/images/emoticon/emoticon07.gif

看更多先前的討論...收起先前的討論...
slime iT邦大師 1 級 ‧ 2016-09-23 13:25:58 檢舉
1. 還是沒有解釋哪些是機械產生或人工輸入? 哪些是用來比對用的基準值?
2. 既然說有拆 S, M, P, 表格就麻煩拆開呈現.
soul81624 iT邦新手 5 級 ‧ 2016-09-23 13:54:12 檢舉
1. 這樣說好了,S是長度規格(基準值),而M是30個樣品量測資料(人工輸入的),我要找出M是介於S規格正負15%的哪個區間;上表的意思會變成,我的量測資料是偏離我的規格,這是要用來做圖表的。
2. 不分開來呈現,是因為第一次發文,還不熟如何呈現較好,所以才用文字說明。
感覺上樓主的方向好像怪怪的.
現在了解:S 跟 M 的關係.
那 P 及 SP 的功能是什麼 ?
在excel 中可以對每個欄位設不同的公式. 但是access 或 sql 不是這樣做的.
以 S 跟 M 的關係. 實際上你只要一個 TABLE 就好了.
如果從 SA 度來看: 檢驗的料號及日期/時間.. 等資料呢?
soul81624 iT邦新手 5 級 ‧ 2016-09-23 16:22:29 檢舉
1. P只是正負15%的區間,由SP可以確認我的M集中在99.5%~100.5%.
2. S、M是用來區分規格跟量測資料.
3. 這裡面是有樣品PK,只是我沒有列出來.
slime iT邦大師 1 級 ‧ 2016-09-26 17:33:34 檢舉
個人會考慮的步驟:
A. 本次基準為 141.5 , 以 1% 為區間, 取 31 個間格, 會有
比分 -15 , 84.5%~85.5% (119.5675~120.9825)
比分 -14 , 85.5%~86.5% (120.9825~122.3975)
...
比分 0 , 99.5%~100.5% (140.7925~142.2075)
比分 +1 , 100.5%~101.5%
....
比分 +15 , 114.5%~115.5%

B. 每次輸入樣本時, 就比對比分表, 查看該次樣本屬於比分多少.

C. 對於樣本的比分加總.
圖片
  直播研討會
圖片
{{ item.channelVendor }} {{ item.webinarstarted }} |
{{ formatDate(item.duration) }}
直播中

2 個回答

3
一級屠豬士
iT邦大師 1 級 ‧ 2016-09-23 15:49:51
最佳解答
create table ithelp160923m (
  id smallserial primary key
, m  float not null
);

insert into ithelp160923m (m) values
(141.68),
(141.75),
(141.64),
(141.77),
(141.72),
(141.7),
(141.75),
(141.76),
(141.72),
(141.75),
(141.78),
(141.7),
(141.72),
(141.75),
(141.76),
(141.65),
(141.68),
(141.68),
(141.72),
(141.75),
(141.78),
(141.75),
(141.74),
(141.75),
(141.76),
(141.75),
(141.72),
(141.78),
(141.76),
(141.75);

create table ithelp160923p (
  id smallserial primary key
, p  float not null
, sp float
);

-- 由 0.855 每次遞增 0.01 到 1.155, 共31筆
insert into ithelp160923p (p)
select generate_series(0.855, 1.155, 0.01) p;

update ithelp160923p
   set sp = 141.5 * p;
---------------------------
-- 最後項 描述不清

with temp as (
select p.id, x
  from ithelp160923p p
  join lateral (
       select count(m) x
         from ithelp160923m m
        where m.m < p.sp 
       ) a
    on true
 union all
select 32, x
  from ithelp160923p p
  join lateral (
       select count(m) x
         from ithelp160923m m
        where m.m >= p.sp 
       ) a
    on true
 where p.id = 31
), temp2 as (
select id
     , x
     , case
         when id = 32 then 0
         else x - coalesce(lag(x) over(),0) 
       end as c
  from temp
)
select t2.id
     , m.m
     , to_char(p.p::real * 100, '999.9') || '%'
     , p.sp
     , t2.x
     , t2.c
  from temp2 t2
  left join ithelp160923p p
    on t2.id = p.id
  left join ithelp160923m m
    on p.id = m.id
 order by t2.id;

+----+--------+----------+----------+----+----+
| id |   m    | ?column? |    sp    | x  | c  |
+----+--------+----------+----------+----+----+
|  1 | 141.68 |   85.5%  | 120.9825 |  0 |  0 |
|  2 | 141.75 |   86.5%  | 122.3975 |  0 |  0 |
|  3 | 141.64 |   87.5%  | 123.8125 |  0 |  0 |
|  4 | 141.77 |   88.5%  | 125.2275 |  0 |  0 |
|  5 | 141.72 |   89.5%  | 126.6425 |  0 |  0 |
|  6 |  141.7 |   90.5%  | 128.0575 |  0 |  0 |
|  7 | 141.75 |   91.5%  | 129.4725 |  0 |  0 |
|  8 | 141.76 |   92.5%  | 130.8875 |  0 |  0 |
|  9 | 141.72 |   93.5%  | 132.3025 |  0 |  0 |
| 10 | 141.75 |   94.5%  | 133.7175 |  0 |  0 |
| 11 | 141.78 |   95.5%  | 135.1325 |  0 |  0 |
| 12 |  141.7 |   96.5%  | 136.5475 |  0 |  0 |
| 13 | 141.72 |   97.5%  | 137.9625 |  0 |  0 |
| 14 | 141.75 |   98.5%  | 139.3775 |  0 |  0 |
| 15 | 141.76 |   99.5%  | 140.7925 |  0 |  0 |
| 16 | 141.65 |  100.5%  | 142.2075 | 30 | 30 |
| 17 | 141.68 |  101.5%  | 143.6225 | 30 |  0 |
| 18 | 141.68 |  102.5%  | 145.0375 | 30 |  0 |
| 19 | 141.72 |  103.5%  | 146.4525 | 30 |  0 |
| 20 | 141.75 |  104.5%  | 147.8675 | 30 |  0 |
| 21 | 141.78 |  105.5%  | 149.2825 | 30 |  0 |
| 22 | 141.75 |  106.5%  | 150.6975 | 30 |  0 |
| 23 | 141.74 |  107.5%  | 152.1125 | 30 |  0 |
| 24 | 141.75 |  108.5%  | 153.5275 | 30 |  0 |
| 25 | 141.76 |  109.5%  | 154.9425 | 30 |  0 |
| 26 | 141.75 |  110.5%  | 156.3575 | 30 |  0 |
| 27 | 141.72 |  111.5%  | 157.7725 | 30 |  0 |
| 28 | 141.78 |  112.5%  | 159.1875 | 30 |  0 |
| 29 | 141.76 |  113.5%  | 160.6025 | 30 |  0 |
| 30 | 141.75 |  114.5%  | 162.0175 | 30 |  0 |
| 31 | [Null] |  115.5%  | 163.4325 | 30 |  0 |
| 32 | [Null] | [Null]   |   [Null] |  0 |  0 |
+----+--------+----------+----------+----+----+
(32 rows)

Time: 1.151 ms
 
--另外,在跟再要搞清楚. 

就算你是用小畫家畫出來的答案
我還是要按個讚
/images/emoticon/emoticon32.gif

soul81624 iT邦新手 5 級 ‧ 2016-09-23 16:35:13 檢舉

我之前是

SELECT COUNT(M)
FROM tableM
WHERE M < (S x 0.855);
UNION
SELECT COUNT(M)
FROM tableM
WHERE M < (S x 0.865);
...
SELECT COUNT(M)
FROM tableM
WHERE M < (S x 1.155);  

這樣可以得到X表,但是C表就不會查了。
而最上面的(查詢2: C表),只是說明Excel中C的算法。

感謝您的分享! 我會再研究前輩分享的語法。

0
海綿寶寶
iT邦大神 1 級 ‧ 2016-09-23 13:18:22

我覺得您還是放棄好了

您講給這裡這麼多人聽,都沒人聽得懂了
講給電腦聽,應該也是聽不懂吧
/images/emoticon/emoticon10.gif

看更多先前的回應...收起先前的回應...
尼克 iT邦大師 1 級 ‧ 2016-09-23 14:00:15 檢舉

說真的,題目看不懂,無從幫起。

soul81624 iT邦新手 5 級 ‧ 2016-09-23 14:16:07 檢舉

真的看不懂,我就再慢慢想吧!謝謝回應/images/emoticon/emoticon06.gif

CalvinKuo iT邦大師 7 級 ‧ 2016-09-23 14:47:51 檢舉

看不懂他的品管檢驗表是要搞啥分析...
他的標準沒有公差阿.... 標準S乘上百分比,這品管也太鬆了吧...
公差乘上百分比才比較有分析作用吧... 不然量測30組都落在99.5%~100.5%.... 連我這種不是品管的人都看不下去....

CalvinKuo iT邦大師 7 級 ‧ 2016-09-23 14:50:14 檢舉

關鍵尺寸 差個幾條就會干涉,造成組裝困難.... 詳細部分我不懂也不多說了。

soul81624 iT邦新手 5 級 ‧ 2016-09-23 14:59:39 檢舉

其實這只是個範例,規格141.5正負1是可接受範圍,所以量出的數據是合格的,而這裡用不到公差做判斷,才沒有打出來;
但是今天不是要討論他的品管如何,我只是要把這份Excel表轉到Access出報表而已!

哦耶!樓下有高人聽懂還寫出來了,掌聲鼓勵鼓勵
/images/emoticon/emoticon34.gif/images/emoticon/emoticon34.gif/images/emoticon/emoticon34.gif

我要發表回答

立即登入回答