G'day all, thanks in advance for reading.
I've got two tables, one that's full of data regularly externally updated called "tblEmployeeLic ences": - EmployeeID,Name,Licences
-
1001,Bill,Drivers
-
1001,Bill,Forklift
-
1002,Ted,Drivers
-
1002,Ted,Forklift
-
1002,Ted,Crane
The other table is one that is depended upon by an ID-card printing program, and as such only allows a single row per employee, called "IDProjectData" :
EmployeeID,Name ,Licence01,Lice nce02,Licence03 ...16
I've developed a nested updated/select query to update only the first Licence for each person (it's trivial to get all of them with slight variations on it): - UPDATE IDProjectData
-
RIGHT JOIN [SELECT TOP 1 Licences
-
FROM tblEmployeeLicences
-
WHERE EmployeeID = [IDProjectData].[EmployeeID] ]. AS Results
-
ON IDProjectData.EmployeeID=Results.EmployeeID
-
SET IDProjectData.IDWLicence01 = Results.Licences;
The above successfully updates the first licence into the IDProjectData table, but the query prompts the user to enter in the CandidateNo for BOTH tables upon running it.
How do I restructure the query so that it just rolls over them all, updating the 'Licence01' column for each employee?
Or should I be going about this a different way?
8 10228
Update - the query is now as follows - UPDATE IDProjectData LEFT JOIN [SELECT TOP 1 Licences
-
FROM tblEmployeeLicences
-
WHERE EmployeeID = [IDProjectData].[EmployeeID] ]. AS Results ON IDProjectData.EmployeeID =Results.EmployeeID SET IDProjectData.IDWLicence01 = Results.Licences;
It still prompts for both EmployeeID's but now it updates ALL employees with that particular Employee's first Licence - how do I get it to look at each employees and update their own first Licence?
FishVal 2,653
Recognized Expert Specialist
Hello.
It looks like you have two copies of the same data which generally is not a good idea.
Since you update [IDProjectData] with values from [tblEmployeeLice nces] I guess data primary source is the latter table.
So, why don't you want to use crosstab query to get dynamically pivotted data from [tblEmployeeLice nces]?
I'm aware that two copies of the same data isn't the best way to do things, but the ID-card software this is designed for uses the [IDProjectData] table, and is very strict about how data is accessed.
I haven't used a crosstab query because I've primarily dealt with SQL up until now - crosstabs and pivots are mostly new to me, and I can't figure out a way to get it to update pivot'd data.
The software that accesses the database can only use THAT particular table, and can only use certain cells in certain ways.
What I'm trying to to develop is a way to replicate data from other tables into that table. The way the data arrives into these other tables is also out of my control - therefore I must make a way to put data in this other table via some form of the above update query, or some sort of updating Crosstab or pivot query - and I've no idea where to get started on CT's or Pivots when it comes to updating data rather than simply selecting it.
FishVal 2,653
Recognized Expert Specialist - First of all it is very probable that the software connecting to [IDProjectData] table could connect to crosstab query with the same name. Check it please.
- Crosstab query being not updateable will give a unupdateable join with [IDProjectData]. So, I suspect it is not possible to update [IDProjectData] this way.
- [IDProjectData] could be a temporary table created from crosstab query via SELECT INTO.
- [IDProjectData] could be updated via recordset.
Unfortunately it doesn't like working from a selected crosstab query - I've tried that. The program refuses to open, giving nothing in the way of error messages =(
I haven't tried the other possible solutions - I've just solved it now. I've used VBA to parse and execute SQL to be fed back into the main update query. It's slow, but it doesn't need to be fast.
Thanks, guys!
NeoPa 32,568
Recognized Expert Moderator MVP
That may be your only solution :(
I'm pretty sure that subqueries in SQL stop it being updatable too.
It's sometimes possible to find sneaky ways around these restrictions, but if you have it processing in code and speed is not a big issue, then that seems like a suitable solution.
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: skidvd |
last post by:
Hello:
I have just recently converted to using the InnoDB table type so that
I can enforce FOREIGN key constraints. I have been using MyISAM
tables (accessed via JDBC) successfully for some time. However, I
have just come across a problem with the new configuration that
boggles my mind....
First some configuration data:
|
by: WGW |
last post by:
Though I am a novice to MS SQL server (2000 I believe), I can do almost!
everything I need. Maybe not efficiently, but usefully. However, I have
a problem -- a complex query problem...
I can create a parameter query in a stored procedure, but how do I use
the result set of a parameter query in a select query (in the same or
another sp)? In short, if a select query contains a result table that is
generated as a parameter query, how do I...
|
by: kalamos |
last post by:
This statement fails
update ded_temp a
set a.balance = (select sum(b.ln_amt)
from ded_temp b
where a.cust_no = b.cust_no
and a.ded_type_cd = b.ded_type_cd
and a.chk_no = b.chk_no
group by cust_no, ded_type_cd, chk_no)
|
by: Anthony Robinson |
last post by:
Have been encountering an odd issue. Every now and again, certain
packages of stored procedures just become invalid. I'm aware that
dropping or altering an underlying table would render a package
invalid, but we are doing no such thing...
After banging my head on the wall for a bit I noticed that the two
stored procedures that are experiencing this behavior are procedures
that are called from within another procedure (they're not both...
|
by: Tcs |
last post by:
My backend is DB2 on our AS/400. While I do HAVE DB2 PE for my PC, I haven't
loaded it yet. I'm still using MS Access. And no, I don't believe this is an
Access question. (But who knows? I COULD be wrong... :) I've tried the
access group...twice...and all I get is "Access doesn't like ".", which I know,
or that my query names are too long, as there's a limit to the length of the SQL
statement(s). But this works when I don't try to...
| |
by: Mark Flippin |
last post by:
I'm evidently not understanding nested triggers and I'm looking for
some help.
I've an Invoice table (see below) that I want to enforce two actions
via after triggers.
The first trigger maintains a set of audit columns in the table
indicating the date on which the row was inserted, the date of the
last change, and the user who made the last change (see below)
|
by: jjturon |
last post by:
Can anyone help me??
I am trying to pass a Select Query variable to a table using Dlookup
and return the value to same select query but to another field.
Ex.
SalesManID SalesManName
AT Alan Time
|
by: Carsten |
last post by:
Hello Folks,
I encountered a problem with SQL server 2000 and UDFs.
I have a scalar UDF and a table UDF where I would like the scalar UDF
to provide the argument for the table UDF like in:
SELECT
*
FROM
|
by: P3Eddie |
last post by:
Hello all!
I don't know if this can even be done, but I'm sure you will either help or suggest another avenue to accomplish the same.
My problem may be a simple find duplicates / do something with only one of the duplicates issue, but I got to the point where an update query with a nested select subquery would work wonderfully, if only it would work!
I have several fields in a master Access 2000 table, some of which are id, fname,...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
| |
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |