473,320 Members | 1,940 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Join Fields from 2 tables

171 100+
Hi All
I have 2 tables, one with records for staff shift data (tbl:duty). The other table (tbl:shiftdefinition) has shiftname and their definitions.
The 'duty' table has a field 'duties' and the records will have multiple characters in this field. The 'shiftdefinition' table has a field 'dutycode' with single character and another field as 'definition'. I need to join this 2 tables on the 'shiftname' and 'dutycode' to get all records from 'duty' table with the definition from the 'shiftdefinition' table.
left(duty!shiftname),1) will be always like left(shiftdefinition!dutycode,1).
I tried a formula in a select query as below:
Expand|Select|Wrap|Line Numbers
  1. GetDef: IIf(Left([duty]![shiftname],1) like Left([shiftdefinition]![dutycode],1) & "*",[shiftdefinition]![definition],"")
But not getting the expected result. I tried all 2 types of joins.
Please guide me
Apr 28 '18 #1
1 1430
NeoPa
32,556 Expert Mod 16PB
I would strongly advise you to strip that all out and start again after reading Database Normalisation and Table Structures.

You've been working in Access for over ten years so I'm very surprised to see such a question from you. And after 170 posts still posting code without the [CODE] tags.
Apr 28 '18 #2

Sign in to post your reply or Sign up for a free account.

Similar topics

0
by: Petre Agenbag | last post by:
Hi List Me again. I'm trying to return from multiple tables, the records that have field "information_sent" between two dates. The tables are all related by means of the id of the entry in the...
9
by: Dave M | last post by:
All I've got a database that keeps track of sales of widgets. Each company that belongs to my organiztion is to report their widget sales or no sales every month. There are several different...
6
by: Maital | last post by:
Dear experts, I'm a beginner with ms-access. I have data from 3 years of observations on a certain field, divided into North and South sections. The first table has information for about 600...
5
by: jim | last post by:
Hi, I've browsed several posts, but still haven't found the answer I'm looking for. I have one table (A) that contains a list of values I want to return. I have two other tables (B) and (C)...
2
by: krishnasarma | last post by:
hi can any one tell me ........how to join two tables of different db(MSAccess) in Vb6 thanx in advance
3
by: kjewell23 | last post by:
Hi. I have look but can't find or understand what I need to do here. I need to join two tables together with a common field which is prdno Here is my code. please help me If I'm doing it wrong. ...
1
by: AccessHunter | last post by:
Please Help.... I have a query with 2 tables, LegalFile and Cases. LegalFile has the field CaseID(Number Field) and Cases has CaseNbr(Text Field). I am trying to find entries that are in...
1
by: AccessHunter | last post by:
Hi, I have a query to join two tables, Table1 - Case (Text), Division (Text) Table2 - CaseID (Text), Division Num (Number) Please help to convert (Number to text) and join the tables in the...
2
by: positivethinking | last post by:
Hello all I am working with oracle 10i I need to join 3 tables in 1 sql statements. (please see attached) it looks simple, but i have spent many hours without any luck, each table have 3...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.