Warm tip: This article is reproduced from serverfault.com, please click
ms-access sql vba

Combining related records with multiple True/False values into a single record

发布于 2020-11-30 16:59:17

I'm trying to combine class meeting data for a large set of classes, based upon multi-column logic. For each class nbr (first column) I can have multiple y/n entries corresponding to whether the class meets that day or not. I need to:

Have a single entry for each ClassNbr/DayOfWeek/MtgStart/MtgEnd combination .

I've used Browne's ConcatRelated() function to great effect to get part of the way there, but I'm stumped with how to get the y/n columns to line up correctly, and I'm stumped on where to go next. This is part of a larger Access 2016 database and needs to stay in it. I'm comfortable using VBA if necessary.

Basic Problem:

enter image description here

Current vs. Expected Real World Result:

enter image description here

Questioner
Stefan Malliet
Viewed
0
分享
Gordon Linoff 2020-12-01 01:10:20

You should be able to use aggregation functions:

select col1, min(a) as a, min(b) as b, min(c) as c
from t
group by col1;

Because MS Access stores "Y" as -1 and "N" as 0, MIN() is equivalent to OR.

Stefan Malliet 2020-11-30 17:41:38

That worked like a charm. Thank you.

Stefan Malliet 2020-12-01 22:41:54

Add'l Note for future users:: The data came in a text, so I had to use a series IIF() functions in a query to convert them to the proper numerical value before proceeding as above. e.g: IIf([a]="Y",-1,0)

热门帖子

1
最近同事的一句话让我一直耿耿于怀，大伙帮忙分析下
2
IPV6 太香了
3
阿里 ECS 突然 CPU 和磁盘 IO 跑满，如何去查，给阿里提工单有用吗
4
我这个到底是什么病，还有救吗
5
有出服务器类发票的吗？
6
打算和媳妇出国旅游，有没有推荐？
7
即将三十岁，辞掉 6 年电子厂的企划的工作，转行成人用品行业的摄影师
8
川西之旅，照片分享
9
榨汁机和榨汁杯有什么推荐吗？有在使用和用过的朋友推荐一下，非常感谢。
10
大陆人工资存香港银行？

热门github

1
Implementation of paper - YOLOv9: Learning What You Want to Learn Using Programmable Gradient Information
2
A Windows and Office activator using HWID / Ohook / KMS38 / Online KMS activation methods, with a focus on open-source code and fewer antivirus detections.
3
Get up and running with Llama 2, Mistral, Gemma, and other large language models.
4
该项目可以让你通过订阅的方式使用Cloudflare WARP+，自动获取流量。This project enables you to use Cloudflare WARP+ through subscription, automatically acquiring traffic.
5
Multi functional app to find duplicates, empty folders, similar images etc.
6
Xray panel supporting multi-protocol multi-user expire day & traffic & ip limit (Vmess & Vless & Trojan & ShadowSocks & Wireguard)
7
The Free Software Media System
8
lightweight, standalone C++ inference engine for Google's Gemma models.
9
📚 Freely available programming books
10
A collective list of free APIs
11
1️⃣🐝🏎️ The One Billion Row Challenge -- A fun exploration of how quickly 1B rows from a text file can be aggregated with Java
12
🎓 Path to a free self-taught education in Computer Science!
13
Curso para aprender el lenguaje de programación Python desde cero y para principiantes. 75 clases, 37 horas en vídeo, código, proyectos y grupo de chat. Fundamentos, frontend, backend, testing, IA...
14
This repository contains System Design resources which are useful while preparing for interviews and learning Distributed Systems
15
Mamba is a new state space model architecture showing promising performance on information-dense data such as language modeling, where previous subquadratic models fall short of Transformers. It is based on the line of progress on structured state space models, with an efficient hardware-aware design and implementation in the spirit of FlashAttention.