admin管理员组文章数量:1356241
I have 2 tables I need information from. "Jobs" and "tempItems".
Jobs table:
| JobID | Item | QTY |
|---|---|---|
| 0007 | Test 1 | 1 |
| 0007 | Test 2 | 1 |
I have 2 tables I need information from. "Jobs" and "tempItems".
Jobs table:
| JobID | Item | QTY |
|---|---|---|
| 0007 | Test 1 | 1 |
| 0007 | Test 2 | 1 |
tempItems:
| Item | QTY |
|---|---|
| Test 1 | 0 |
| Test 2 | 0 |
| Test 3 | 0 |
| Test 4 | 0 |
I need results from the Jobs table AND the tempItems that isn't in the Jobs table
| Item | QTY | (explanation) |
|---|---|---|
| Test 1 | 1 | <-- from jobs table |
| Test 2 | 1 | <-- from jobs table |
| Test 3 | 0 | <-- from tempItems table |
| Test 4 | 0 | <-- from tempItems table |
I just can't seem to wrap my head around it.
Share Improve this question edited Mar 31 at 19:48 marc_s 756k184 gold badges1.4k silver badges1.5k bronze badges asked Mar 31 at 11:00 Bill EBill E 11 silver badge New contributor Bill E is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct. 1- 1 You can't having done much research on this. A left outer join between Item will do - Access even has a wizard that will help you to achieve this. – Gustav Commented Mar 31 at 11:24
2 Answers
Reset to default 1As @Gustav said - the wizard will help you create an "Unmatched Query" to return everything in tempItems that isn't in Jobs:
SELECT tempItems.Item, tempItems.Qty
FROM tempItems LEFT JOIN Jobs ON tempItems.[Item] = Jobs.[Item]
WHERE (((Jobs.Item) Is Null));
Then all you need to do is Union that with a query returning everything from the Jobs table (and remove all the brackets - Access just loves brackets).
SELECT Item,
Qty
FROM Jobs
UNION SELECT tempItems.Item,
tempItems.Qty
FROM tempItems LEFT JOIN Jobs ON tempItems.Item = Jobs.Item
WHERE Jobs.Item Is Null
There's no need to name the table for each field in the first query - Jobs is the only table being used.
The field names in the second (unioned) table need the table name as Access won't know if you mean the field in tempItems or Jobs.
I've removed all the brackets (square brackets are only need around fields with a space in).
Try construct query
SELECT iif(tempItems.Item is not null,tempItems.Item,Jobs.Item) as [Item],
nz(nz(Jobs.QTY,tempItems.QTY),0)
FROM tempItems LEFT JOIN Jobs ON tempItems.Item = Jobs.Item;
本文标签：
版权声明：本文标题：database - Get some records from one table then get records that don't match those from another table - Stack Overflow 内容由网友自发贡献，该文观点仅代表作者本人， 转载请联系作者并注明出处：http://www.betaflare.com/web/1743952427a2567532.html， 本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容，一经查实，本站将立刻删除。
发表评论