Question About Updateable Table Layout

Jan 22, 2008

Hey all.. Been out of the Access loop for quite some time now.. Last project I did was in Acc97, about 5 years ago! Been working on SQL Server and web programming lately...

Anyway, I have a project that needs to be done in Access XP. It is a basic resource allocation DB. Listing of employees, projects, and time associated with those projects per Employee. I want to keep this normalized, and so I have this basic table layout..

tblEmployee -- Has EmployeeID, name, etc
tblProject -- Has ProjectID, name, etc
tblEmployeeProject -- Has EmployeeID, ProjectID, year, month, hours
tblWorkingHours -- Has an autoNumber ID, Year, Month, Working Hours, Holidays, business days

Basically, the tblWorkingHours has 12 entries per year, Jan-Dec.
The tblEmployeeProject is where I have the meat of my data.. If an employee(1) is assigned to a project(123) for 20 hours a month for 3 months(Jan-Mar 2008), then there will be 3 rows in there.
EmployeeID - ProjectID - Year - Month - Hours
1 - 123 - 2008 - 1 - 20
1 - 123 - 2008 - 2 - 20
1 - 123 - 2008 - 3 - 20

This seems to make sense to me, and seems to be normalized. However, I cannot seem to create an updatable query on this layout... I would like to pick a project, and then a few employees assigned to that project, and then enter in the hours for each employee, for any of the 12 months I need. The problem I'm having is that I cannot seem to get an updatable query out of this layout. The only way for me to get a listing of all employees attached to a project, and then all months for the year, regardless of an entry in tblEmployeeProject, is to use a Cartesian Product type join on tblEmployee and tblWorkingHours. And then do an outside join on tblEmployeeProject. However this is obviously not updatable.

The only other option I can think of, is to prefill the tblWorkingHours table with 12 entries per year, however this seems to add unnecessary data in the database. At that point, I might as well create a table that has 12 columns, one for each month.

Sorry for the long-winded post, but wanted to try to get all my points across. Any help would be greatly appreciated! Thanks all.

View Replies


ADVERTISEMENT

Table Layout

Apr 11, 2007

17099Good Morning

I was wondering if someone can please assist/advice. I am currently a bit stuck and feeling like I am walking into a dead end the whole time.
I am currently creating a DB with the purpose of Capturing Potential Candidates provide them with an ID. Fill in their basic details Name Surname, submit to a report, which I will then export, email to them, they will fill in the outstanding information requested, send it back and will import it back into the DB with my various queries to run searches on candidates for placements.

My only problem is that I have quite a lot of fields...

So I Decided to Create 4 Tables
In each table I am using the same ID for a person so should be a 1 to 1 Relationship.



1 General Information
2 Info Relating to Skill
3 Work History
4 Qualifications.


I have all of these separate tables due to the fact that with all the records in one i exceed my 255 table fields allowed

But then if I decide to create a query so I can run some of the details i get errors because there are to many fields. Even if i run one on the other. My last one will still have to many fields.
Basically I am hoping someone can just assist in what would be the best way for me to lay out my tables. And what would be the best to link these tables.
Is it better to have all the records horizontally, or as a one to many with duplicates.

Please find attached a copy of the tables. I would really appreciate any advice
Regards

View 3 Replies View Related

Complex Table Layout Help

Jun 27, 2005

I am working on a complex database to store Business information as well as specific into about that Business... here is the tricky part...

The individual companies are sorted by Business type (i.e. Computer Software Companies, Child Care Providers, Commercial Building Contractors, etc.) and each Business type needs to hold a simi-customized set of information (i.e. Commercial Building Contractors needs Total AZ Billings for Commercial Bldg., $ Awarded for Completed Contracts, % of Work Subcontracted, No. of Local Employees, and Areas of Specialty). I will refer to each type of information as a Category. Each Business Type has between 4 and 7 Categories. Within a Business Type there are between 10 and 30 companies listed. There will be just over 200 Business Types listed within my database structure, as well as around 100 unique types of Categories.

Primarily, I need to be able to query and format each Business Type, to include the Companies information as well as each Category that is associated with the Business in a flat spreadsheet style layout.

Now, I intend to create a unique report (if needed) for each Business Type as needed to customize the look (i.e. column widths and any other special formatting needed) of each Business Type,

Here is where I stand currently with this project…

I have 4 tables,
1. Business (it includes basic contact info that is common to all business)
2. Category (is basically a list of all possible categories)
3. BusinessCategory (a Many-to-Many relationship container that links the BusinessID to the CategoryID and includes the corresponding Value)
4. BusinessType (a list of business types that a business can belong to)

This structure seems to work in that it can handle the customization of categories for each Business within a BusinessType. The problem I am running into with this structure is creating a query to handle the data and create a “flat” table for a BusinessType with each Business and it’s categories.

It may be that I can use this structure to do what I need done, however I think that there must be a better way to structure my data to work the way I need it to work. I really appreciate your input on this!

View 2 Replies View Related

Please Help - Does My Table Layout And Relationships Look Ok?

May 2, 2007

Hello everyone.

Im just wondering if anyone can find any problems with my table layouts or relationships are anything else?

The ER-D can be found here:
(URL address blocked: See forum rules)=17320&d=1178117475

Thanks, Ant...

View 3 Replies View Related

Tables :: Checking Table Layout?

Apr 9, 2013

We have an application which is used in a couple of offices and on a number of terminals. The data is stored in a back-end database on the server on each site.The databases are getting to the stage that we cannot roll out changes to the back end table layouts relatively efficiently. What I want to do through vba if possible is basically have a blank local version of each table and say:

check tab_1 on linked_backend, if tab_1 layout <> tab_1_local layout then update the back end layout to mirror that of the local layout.

We cannot overwrite the data, but on occasion, we need to add an extra field to a central table as new bits of the application are developed.I've tried a few searches here, and am probably using the wrong phrases, but not getting any results.

View 4 Replies View Related

Table Layout For Airport Minicab Quote System

Jan 3, 2006

ok basically im creating a quote system that will show users prices from one destination to the other destination based on the size of the vehicle..

you can see a similar quote system used on the following website..
http://www.londonairporttaxi.co.uk

there will be three main tables..

Airport Table
airportID - primary key, autonumber
airportNAME - text

the airport table will consist of 5 records..Heathrow,Gatwick,Stansted,London City, Luton

Town Table
TownID - primary key, autonumber
TownNAME - text

the town table will contain a list of postcodes, maybe 50 or so (n1,n2,n3,n4,e1,e2...)

Car Table
CarID - primary key, autonumber
CarNAME - text

the car table will consist of 4 records..Saloon,Estate,MPV,Executive

now what i need to do is create a similar quote system like there is on the url above..

my original idea would be to do something like this..

Have three tables that contain the quotes for each type of car..the first two listed here will work i think..


Airport2TownQuotes Table
quoteID - primary key, autonumber
carID - foreign key from car table
airportID - the airport Pickup, foreign key
townID - the town Destination, foreign key
price - currency

Town2AirportQuotes Table
quoteID - primary key, autonumber
carID - foreign key from car table
townID - the town Pickup, foreign key
airportID - the airport Destination, foreign key
price - currency

however.. for airport to airport quotes.. the resembling table would look something like this..

Airport2AirportQuotes Table
quoteID - primary key, autonumber
carID - foreign key from car table
airportID - the airport Pickup, foreign key
airportID - the airport Destination, foreign key
price - currency

but you cannot place the airport id twice in the same table..(this maybe a case of simply renaming the airport fields in this table such as airport1, and airport2.. but can you still link them and enforce integrity if the fields have different names?)

also one example may be Heathrow to n1 is £30... this does not mean however that n1 to heathrow will be £30.. this is why i need to separate the quotes in this way.. for the 3 possible journeys (airport to airport, town to airport, airport to town).. also the quotes will be different for each car type..

anyway.. i shall be using asp to create a similar quote system on my website.. but i just need to get the final layout of the database sorted.

thanks in advance for any help.

View 7 Replies View Related

Forms :: Check Box In A Form Which Shows Data From A Table In Tabular Layout

May 12, 2013

I have made an unbound check box, named "myckeck" in a form which shows data from a table in tabular layout.

1- When I check one check box, all check boxes are checked. How can I solve that? I mean I want to check or clear check boxes independently.

2- I made a text box, named "jobdate" to sow todays date automatically upon checking a check box.

Code:
Private sub mycheck_afterupdate()
jobdate = date
End

But this is not working and checking a check box does not make anything to happen.

3- Then I made this expression in the default value of "jobdate" properties:

iif( mycheck = true, date(), null)

This not working either.

View 3 Replies View Related

Why Is This Not Updateable

May 26, 2006

In trying to help another member of these forums, I have this query:SELECT [Table1].[name], [Table1].[age], [Table1].[telephone], [Table1].[address], [Table1].[postcode], [tbl3].[ImageBMP]
FROM Table1, tbl3
WHERE [tbl3].[ImageID]=IIf(IsNull([postcode]),2,1);
Basically, the idea is that the image from tbl3 changes if the postcode field is null.

The WHERE clause seems to make this non-updateable, which I don't understand. Any explanation why, and how can I achieve the same result with an updateable result?

View 7 Replies View Related

This Record Set Is Not Updateable

Jul 5, 2005

this record set is not updateable

Me.TimeSpan = Now() - CDate(Me.ArchiveDate)

View 1 Replies View Related

Query Not Updateable?!?

Sep 29, 2005

I have a form that is based on a query that has all of the sudden become non-updateable. After searching this site for a while - I found someone who had a similar problem, it was reccomended to check the query the form is based on to see if it could be updated. I did that with mine and the query COULD NOT be updated either.

To the best of my knowledge - nothing has changes with the tables the query is based off of or the query itself.

Any ideas what else to look for?

View 3 Replies View Related

Recordset Not Updateable

Apr 7, 2006

I'm having a problem updating a form. The query from the form is based on two other queries, one of these queries is based on a table from an external database.

I want to use the Form to populate only data on my own database using a checkbox with:

If IsNull (Me.ReceiptDate) Then
ReceiptDate= ExternalReceiptDate 'where ExternalReceiptDate is the receipt date from the external table
End If

I have checked to make sure that the "unique values" of the queries are all set to No.

As I will only be updating data to tables that are on my database that are updatable. I'm hoping that someone can offer some advice if this is possible and why I'm still getting "recordset not updatable" when trying to enter any data to my data fields in the form.

Thanks

View 3 Replies View Related

Query Not Updateable

Oct 18, 2007

Having a problem that's driving me nuts

I am trying to ship some inventory from a sales order line (query 2qryInvShip works fine) but when I join the query with another (to find the oldest stock) Shipped Qty, Shipped Location & Shipped checkbox cannot be updated

Any thoughts as to where I am going wrong with this much appreciated, I have have tried seemingly endless combinations of join types etc but to no avail

db attached

View 7 Replies View Related

“This Recordset Is Not Updateable”

Dec 9, 2007

Hi,

I am trying to check a “Check Box” on my query, but Access won’t allow me to do so as “This recordset is not updateable” - Could anyone please offer an idea of how I can get around this problem…..

I buy lots of products from a supplier, for each product ordered I assign an order ID, and have a check box called “Delivered” for each product to show whether it has been delivered or not. When the delivery arrives I print out our order onto paper and tick each product that has arrived, then I go onto the Access database and tick all of the boxes for the products that have arrived.

This way, any products that haven’t arrived and have not been checked show up on a query to show all products that have the “Delivered” field unchecked.

9 times out of 10 all of the products arrive, so I want to perform a group query on the order ID, and then check the box for the whole order to show that all the products arrived – rather than having to tick off each product (sometimes there can be up to 50 products, and I’ve already gone through the process once on paper so if they are all there I don’t want to have to do it all over again).

For example, the following shows the printed report for the order “1234”:

[Order ID] [Product ID] [Supplier ID] [Delivered]
1234 1 Supplier A NO
1234 2 Supplier A NO
1234 3 Supplier A NO
1234 4 Supplier A NO
1234 5 Supplier A NO
1234 6 Supplier A NO
1234 7 Supplier A NO
1234 8 Supplier A NO
1234 9 Supplier A NO

I’ve been through the delivery from Supplier A and all of the products are there.
I perform a group query on the “Order ID”, “Supplier ID” and “Delivered” fields to return the following

[Order ID] [Supplier ID] [Delivered]
1234 Supplier A NO

I want to be able to tick the “Delivered” check box and it automatically check each “Delivered” box for the 9 products mentioned above.

Does anyone know if this is possible? I’ve spent ages going through the properties of the query, changing the Recordset Types and trying to use forms, and going through help files but I can’t find any options. I did come across the possibility of using an update query – however I don’t know how to perform a “Group By” total on the “Order ID”.

Any help or advice would be very much appreciated.

Thank you for your time.

Dean

View 4 Replies View Related

Recordset Not Updateable?

Mar 3, 2005

I want to enter info in a form and the following message gets displayed: recordset not updateable?

What do I have to change?

Rahel

View 5 Replies View Related

Predicate For Non-updateable Query...

May 17, 2005

Okay, it may seem like a silly question for one who's been using this as long as I but I was just curious if anyone had a concise answer as to the predicate for a non-updateable query?

Today I think I decided to stop using a workaround by creating a temporary table with duplicate information found elsewhere in the system.

~Chad

View 1 Replies View Related

Operation Must Use An Updateable Query?

Mar 7, 2006

Hello,

I was hoping someone on this forum might be able to help me.

I am trying to update a table with info in another table, using a nested query.

strSQL = "UPDATE tblTemp SET AdminNum = '" & cboAdmin.Text & "', " & _
"Size = (SELECT Size FROM tblEquipList WHERE AdminNum = '" & cboAdmin.Text & "')"

DoCmd.RunSQL strSQL

cboAdmin is a combo box on my form that is populated by tblEquipList.AdminNum.

When run this gives me:
Runtime Error '3073':
Operation must use an updateable query.

The SELECT statement works by itself, and the first part of the UPDATE (without the nested select) works by itself, but not when I combine them. I also need to update other things in the same manner in which Size is being updated, but when I can get this to work it shouldn't be a problem just added the updates for the other fields in there.

Thanks in advance,
Jeremiah


Edit:
Also, I figured I should add that when I remove the SELECT statment and put in a number (IE: Size = '400'), the query runs just fine. So I know the problem lies in the nesting, but am unsure how.

View 7 Replies View Related

Problem With Updateable Query

Jun 1, 2007

Hi everyone,

I have four tables (all linked by a unique identifier) which I populate via data entry forms. I would like to give the user an option of making changes to the data by entering the unique identifier and displaying the specific record (from all four tables).

Is there any way to write an updateable query to accomplish this task?

I started with a query of two tables and this was updateable.

I then added a third table and now it don't work.

Here's the SQL statement
-----------------------------------------------------------------
SELECT tblPerson.*, tblPermaddr.*, tblTempaddr.*
FROM (tblPerson INNER JOIN tblPermaddr ON tblPerson.FILENO = tblPermaddr.FILENO) INNER JOIN tblTempaddr ON tblPerson.FILENO = tblTempaddr.FILENO
WHERE (((tblPerson.FILENO)=[enter fileno]));
-----------------------------------------------------------------

Any suggestions??


Many thanks in advance.

Will

View 1 Replies View Related

Updateable Drop Downlists

Nov 10, 2005

I have a form that has normal text boxes for data input and a couple drop down boxes that look at another table for the selection choices. Everything is great. What I want to do is give the user a drop down list but also give them the ability to add to that look up table. Where if the choice is not in the drop down list they can type in a new value and it will update the Look up table so it will show up the next time.

Does anyone know how I would go about that?

Thanks in Advance

View 3 Replies View Related

Operation Must Use An Updateable Query.

Mar 21, 2006

I have tried to create a form page that when filled in by a user updates a database.

However i get his error messge

Code:Error Type:Microsoft OLE DB Provider for ODBC Drivers (0x80004005)[Microsoft][ODBC Microsoft Access Driver] Operation must use an updateable query./tpvsite/RegisterUser.asp, line 62

In my page it aligns to this line objRS.Update. Which i think means there is a problem with my database as anyone any ideas how to fix this. (all my tables and fields are correct)

Cheers

View 5 Replies View Related

Forms :: How To Set A Field As Updateable

Nov 12, 2014

I am trying to input info into my form

I got

Me.TotalBC1.Value = Me.TotalBC1.Value

Now it's saying the "Recordset is not updateable" and I can't find it for the textbox where to change it to an updateable field

View 14 Replies View Related

Operation Must Be Updateable Query Error?

Mar 9, 2006

Can someone tell me why this query is causing this error? I don't understand it:

UPDATE 1_2_06 SET ROLE = (SELECT ROLE FROM UserRoleData WHERE USERNAME=ASSIGNED_TO)
WHERE EXISTS (SELECT ROLE FROM UserRoleData WHERE USERNAME=ASSIGNED_TO);

thank you

View 1 Replies View Related

Union Query Datasheet Not Updateable

Mar 19, 2008

Hello All,
I have a database with multiple tables, each table with a subset of common fields I need to work with. The way the database was set up was a series of append queries that created a common table to combine the subset of fields. The problem with this is that I had to work with the common table but update the data in the parent tables and regenerate common table to keep everything up to date. To get around this I have created a union query to replace the common table but the data is not updatable. I have had a quick scan of the net and it seems union queries are not updateable by default. I understand the problem I have is a result of the database not being structured correctly however due to circumstances beyond my control, I am stuck with it. I have come across a couple of INSTEAD OF trigger examples but not sure quite how they work and if they can help me. Below is the SQL code for my union query so any help in making it updatable would be greatly appreciated.

Regards

Daz

SELECT [Belt Filter Press].Table, [Belt Filter Press].Plant, [Belt Filter Press].[New Tag], [Belt Filter Press].[New Comment],

[Belt Filter Press].[P&ID number], [Belt Filter Press].Purpose, [Belt Filter Press].[Motor Size], [Belt Filter Press].Starting, [Belt

Filter Press].New, [Belt Filter Press].Comment, [Belt Filter Press].Switchboard, [Belt Filter Press].PLC, [Belt Filter

Press].[PLC Interface], [Belt Filter Press].ACH, [Belt Filter Press].IL, [Belt Filter Press].ZSO, [Belt Filter Press].WIO, [Belt

Filter Press].ZSP, [Belt Filter Press].PSL, [Belt Filter Press].FSL, [Belt Filter Press].TSH, [Belt Filter Press].MSX, [Belt Filter

Press].SSL
FROM [Belt Filter Press];

UNION SELECT Bins.Table, Bins.Plant, Bins.NewTag, Bins.[New Comment], Bins.[P&ID number], Bins.Purpose, Bins.[Motor

Size], Bins.Starting, Bins.New, Bins.Comment, Bins.Switchboard, Bins.PLC, Bins.[PLC Interface], Bins.ACH, Bins.IL,

Bins.ZSO, Bins.WIO, Bins.ZSP, Bins.PSL, Bins.FSL, Bins.TSH, Bins.MSX, Bins.SSL
FROM Bins;

UNION SELECT [Chemical Dosing].Table, [Chemical Dosing].Plant, [Chemical Dosing].[New Tag], [Chemical Dosing].[New

Comment], [Chemical Dosing].[P&ID number], [Chemical Dosing].Purpose, [Chemical Dosing].[Motor Size], [Chemical

Dosing].Starting, [Chemical Dosing].New, [Chemical Dosing].Comment, [Chemical Dosing].Switchboard, [Chemical

Dosing].PLC, [Chemical Dosing].[PLC Interface], [Chemical Dosing].ACH, [Chemical Dosing].IL, [Chemical Dosing].ZSO,

[Chemical Dosing].WIO, [Chemical Dosing].ZSP, [Chemical Dosing].PSL, [Chemical Dosing].FSL, [Chemical Dosing].TSH,

[Chemical Dosing].MSX, [Chemical Dosing].SSL
FROM [Chemical Dosing];

UNION SELECT Clarifiers.Table, Clarifiers.Plant, Clarifiers.[New Tag], Clarifiers.[New Comment], Clarifiers.[P&ID number],

Clarifiers.Purpose, Clarifiers.[Motor Size], Clarifiers.Starting, Clarifiers.New, Clarifiers.Comment, Clarifiers.Switchboard,

Clarifiers.PLC, Clarifiers.[PLC Interface], Clarifiers.ACH, Clarifiers.IL, Clarifiers.ZSO, Clarifiers.WIO, Clarifiers.ZSP,

Clarifiers.PSL, Clarifiers.FSL, Clarifiers.TSH, Clarifiers.MSX, Clarifiers.SSL
FROM Clarifiers;

UNION SELECT Filters.Table, Filters.Plant, Filters.[New Tag], Filters.[New Comment], Filters.[P&ID number], Filters.Purpose,

Filters.[Motor Size], Filters.Starting, Filters.New, Filters.Comment, Filters.Switchboard, Filters.PLC, Filters.[PLC Interface],

Filters.ACH, Filters.IL, Filters.ZSO, Filters.WIO, Filters.ZSP, Filters.PSL, Filters.FSL, Filters.TSH, Filters.MSX, Filters.SSL
FROM Filters;

UNION SELECT [Gravity Drainage Deck].Table, [Gravity Drainage Deck].Plant, [Gravity Drainage Deck].[New Tag], [Gravity

Drainage Deck].[New Comment], [Gravity Drainage Deck].[P&ID number], [Gravity Drainage Deck].Purpose, [Gravity Drainage

Deck].[Motor Size], [Gravity Drainage Deck].Starting, [Gravity Drainage Deck].New, [Gravity Drainage Deck].Comment,

[Gravity Drainage Deck].Switchboard, [Gravity Drainage Deck].PLC, [Gravity Drainage Deck].[PLC Interface], [Gravity Drainage

Deck].ACH, [Gravity Drainage Deck].IL, [Gravity Drainage Deck].ZSO, [Gravity Drainage Deck].WIO, [Gravity Drainage

Deck].ZSP, [Gravity Drainage Deck].PSL, [Gravity Drainage Deck].FSL, [Gravity Drainage Deck].TSH, [Gravity Drainage

Deck].MSX, [Gravity Drainage Deck].SSL
FROM [Gravity Drainage Deck];

UNION SELECT [Grit Classifiers].Table, [Grit Classifiers].Plant, [Grit Classifiers].[New Tag], [Grit Classifiers].[New Comment],

[Grit Classifiers].[P&ID number], [Grit Classifiers].Purpose, [Grit Classifiers].[Motor Size], [Grit Classifiers].Starting, [Grit

Classifiers].New, [Grit Classifiers].Comment, [Grit Classifiers].Switchboard, [Grit Classifiers].PLC, [Grit Classifiers].[PLC

Interface], [Grit Classifiers].ACH, [Grit Classifiers].IL, [Grit Classifiers].ZSO, [Grit Classifiers].WIO, [Grit Classifiers].ZSP, [Grit

Classifiers].PSL, [Grit Classifiers].FSL, [Grit Classifiers].TSH, [Grit Classifiers].MSX, [Grit Classifiers].SSL
FROM [Grit Classifiers];

UNION SELECT Instruments.Table, Instruments.Plant, Instruments.[New Tag], Instruments.[New Comment],

Instruments.[P&ID Number], Instruments.Purpose, Instruments.[Motor Size], Instruments.Starting, Instruments.New,

Instruments.Comment, Instruments.Switchboard, Instruments.PLC, Instruments.[PLC Interface], Instruments.ACH,

Instruments.IL, Instruments.ZSO, Instruments.WIO, Instruments.ZSP, Instruments.PSL, Instruments.FSL, Instruments.TSH,

Instruments.MSX, Instruments.SSL
FROM Instruments;

UNION SELECT Mixer.Table, Mixer.Plant, Mixer.[New Tag], Mixer.[New Comment], Mixer.[P&ID number], Mixer.Purpose,

Mixer.[Motor Size], Mixer.Starting, Mixer.New, Mixer.Comment, Mixer.Switchboard, Mixer.PLC, Mixer.[PLC Interface],

Mixer.ACH, Mixer.IL, Mixer.ZSO, Mixer.WIO, Mixer.ZSP, Mixer.PSL, Mixer.FSL, Mixer.TSH, Mixer.MSX, Mixer.SSL
FROM Mixer;

UNION SELECT [Odour Control].Table, [Odour Control].Plant, [Odour Control].[New Tag], [Odour Control].[New Comment],

[Odour Control].[P&ID number], [Odour Control].Purpose, [Odour Control].[Motor Size], [Odour Control].Starting, [Odour

Control].New, [Odour Control].Comment, [Odour Control].Switchboard, [Odour Control].PLC, [Odour Control].[PLC Interface],

[Odour Control].ACH, [Odour Control].IL, [Odour Control].ZSO, [Odour Control].WIO, [Odour Control].ZSP, [Odour

Control].PSL, [Odour Control].FSL, [Odour Control].TSH, [Odour Control].MSX, [Odour Control].SSL
FROM [Odour Control];

UNION SELECT [Pump List].Table, [Pump List].Plant, [Pump List].[New Tag], [Pump List].[New Comment], [Pump List].[P&ID

number], [Pump List].Purpose, [Pump List].[Motor Size], [Pump List].Starting, [Pump List].New, [Pump List].Comment,

[Pump List].Switchboard, [Pump List].PLC, [Pump List].[PLC Interface], [Pump List].ACH, [Pump List].IL, [Pump List].ZSO,

[Pump List].WIO, [Pump List].ZSP, [Pump List].PSL, [Pump List].FSL, [Pump List].TSH, [Pump List].MSX, [Pump List].SSL
FROM [Pump List];

UNION SELECT [Safety Shower].Table, [Safety Shower].Plant, [Safety Shower].[New Tag], [Safety Shower].[New Comment],

[Safety Shower].[P&ID number], [Safety Shower].Purpose, [Safety Shower].[Motor Size], [Safety Shower].Starting, [Safety

Shower].New, [Safety Shower].Comment, [Safety Shower].Switchboard, [Safety Shower].PLC, [Safety Shower].[PLC

Interface], [Safety Shower].ACH, [Safety Shower].IL, [Safety Shower].ZSO, [Safety Shower].WIO, [Safety Shower].ZSP,

[Safety Shower].PSL, [Safety Shower].FSL, [Safety Shower].TSH, [Safety Shower].MSX, [Safety Shower].SSL
FROM [Safety Shower];

UNION SELECT [Screenings Equipment].Table, [Screenings Equipment].Plant, [Screenings Equipment].[New Tag],

[Screenings Equipment].[New Comment], [Screenings Equipment].[P&ID number], [Screenings Equipment].Purpose,

[Screenings Equipment].[Motor Size], [Screenings Equipment].Starting, [Screenings Equipment].New, [Screenings

Equipment].Comment, [Screenings Equipment].Switchboard, [Screenings Equipment].PLC, [Screenings Equipment].[PLC

Interface], [Screenings Equipment].ACH, [Screenings Equipment].IL, [Screenings Equipment].ZSO, [Screenings

Equipment].WIO, [Screenings Equipment].ZSP, [Screenings Equipment].PSL, [Screenings Equipment].FSL, [Screenings

Equipment].TSH, [Screenings Equipment].MSX, [Screenings Equipment].SSL
FROM [Screenings Equipment];

UNION SELECT Screens.Table, Screens.Plant, Screens.[New Tag], Screens.[New Comment], Screens.[P&ID number],

Screens.Purpose, Screens.[Motor SIze], Screens.Starting, Screens.New, Screens.Comment, Screens.Switchboard,

Screens.PLC, Screens.[PLC Interface], Screens.ACH, Screens.IL, Screens.ZSO, Screens.WIO, Screens.ZSP, Screens.PSL,

Screens.FSL, Screens.TSH, Screens.MSX, Screens.SSL
FROM Screens;

UNION SELECT [Surface Aerator].Table, [Surface Aerator].Plant, [Surface Aerator].[New Tag], [Surface Aerator].[New

Comment], [Surface Aerator].[P&ID number], [Surface Aerator].Purpose, [Surface Aerator].[Motor Size], [Surface

Aerator].Starting, [Surface Aerator].New, [Surface Aerator].Comment, [Surface Aerator].Switchboard, [Surface Aerator].PLC,

[Surface Aerator].[PLC Interface], [Surface Aerator].ACH, [Surface Aerator].IL, [Surface Aerator].ZSO, [Surface Aerator].WIO,

[Surface Aerator].ZSP, [Surface Aerator].PSL, [Surface Aerator].FSL, [Surface Aerator].TSH, [Surface Aerator].MSX, [Surface

Aerator].SSL
FROM [Surface Aerator];

UNION SELECT Tanks.Table, Tanks.Plant, Tanks.[New Tag], Tanks.[New Comment], Tanks.[P&ID number], Tanks.Purpose,

Tanks.[Motor Size], Tanks.Starting, Tanks.New, Tanks.Comment, Tanks.Switchboard, Tanks.PLC, Tanks.[PLC Interface],

Tanks.ACH, Tanks.IL, Tanks.ZSO, Tanks.WIO, Tanks.ZSP, Tanks.PSL, Tanks.FSL, Tanks.TSH, Tanks.MSX, Tanks.SSL
FROM Tanks;

UNION SELECT [UV System].Table, [UV System].Plant, [UV System].[New Tag], [UV System].[New Comment], [UV

System].[P&ID number], [UV System].Purpose, [UV System].[Motor Size], [UV System].Starting, [UV System].New, [UV

System].Comment, [UV System].Switchboard, [UV System].PLC, [UV System].[PLC Interface], [UV System].ACH, [UV

System].IL, [UV System].ZSO, [UV System].WIO, [UV System].ZSP, [UV System].PSL, [UV System].FSL, [UV

System].TSH, [UV System].MSX, [UV System].SSL
FROM [UV System];

UNION SELECT Valves.Table, Valves.Plant, Valves.[New Tag], Valves.[New Comment], Valves.[P&ID number],

Valves.Purpose, Valves.[Motor Size], Valves.Starting, Valves.New, Valves.Comment, Valves.Switchboard, Valves.PLC,

Valves.[PLC Interface], Valves.ACH, Valves.IL, Valves.ZSO, Valves.WIO, Valves.ZSP, Valves.PSL, Valves.FSL, Valves.TSH,

Valves.MSX, Valves.SSL
FROM Valves;

View 1 Replies View Related

Operation Must Use An Updateable Query - MS Access.

Nov 16, 2004

I am trying to update rows in an access database by retrieving data from dynamic text boxes created through a loop. I keep getting the error message: System.Data.OleDb.OleDbException: Operation must use an updateable query. My code is as follows:

Dim myInsertQuery AsString

myInsertQuery = "INSERT INTO tblTestQues (Answer) Values ("txt" & loop1);"

Dim myCommand2 AsNew OleDbCommand(myInsertQuery)

myCommand2.Connection = MyConnection

MyConnection.Open()

myCommand2.ExecuteNonQuery()

myCommand2.Connection.Close()

Please help or contact me if you need more information. Thanks in advanced.

View 3 Replies View Related

ERROR: Operation Must Use An Updateable Query.

Mar 25, 2007

asp code:
Code:<%Set Con = Server.CreateObject("ADODB.Connection") Con.Open "db"dim username, password, mailusername = Request.form("username")password = Request.form("password")mail = Request.form("mail")sql= "INSERT INTO users(username) values('" & username & "')"Con.Execute sqlCon.closeset Con = nothing%>

error:
Code:Error Type:Microsoft OLE DB Provider for ODBC Drivers (0x80004005)[Microsoft][ODBC Microsoft Access Driver] Operation must use an updateable query./site/content/register_act.asp, line 13

It's says it has something to do with permissions...

I set writing & reading permissions for the db and the directory that contains it through
contorl panel->Administrative Tools->Internet Information Services

I have no "security tab" when I rightclick->properties the db file or it's directory even though the file system is NTFS

in spite all these when I rightclick->properties the directory that contains the db I see a "read only" square marked in the general tab-attributes, when I try to unmark it seems to be unmarked but when I rightclick->properties again it's marked again...

what should I do?

View 1 Replies View Related

Queries :: SQL Code - Recordset Is Not Updateable?

Oct 4, 2013

I'm fairly new to SQL and I'm trying to input data into this form I've created but it tells me that "this Recordset is not updateable". Here's my SQL code, I'm not certain this is the problem though.

Code:
SELECT [Annual Instructor Report County Summary].*, [Camps, SS Events, Events, Total Members].*,
[Archery Members].[numArchClubs]+[Archery Members].[numArchNon4H]+[Archery Members].[numArchCamp]+
[Archery Members].[numArchSSEvent]+[Archery Members].[numArchEvent] AS totArchMem, [Rifle Members].

[Code] ....

View 6 Replies View Related

Creating Updateable Datasheet Based On Mult. Queries

Feb 12, 2006

Hello all,

I have a scheduling database that I am only using one table "DateLog"

It contains these fields:
JobIDInt-PK
LotIDTxt-PK
TaskIDInt-PK - List from TaskList Table
TripIDInt-PK
TaskDateDate
ForemanIDInt - List from Foreman Table
TaskQtyInt
TaskMisc1Txt
TaskMisc2Txt
NotesMemo


Every time a trip is taken to a job site, a record is created. I would like to have a datasheet view of this table that will join all of the tasks for a particular JobID and LotID together on the same record for easy viewing.
The list of tasks are static and will not change from job to job. The only fields that needs to be seen are the JobID and LotID at the row header. The rest of the data is the TaskDate for each task.

What I have attempted so far is to create a separate query for each TaskID and then to join them by the JobID and LotID. I have gotten to the point that I can put values into the datasheet, but I get an error that the primary key cannot be null.

Am I approaching this the correct way? Is there a way I can point the hidden key fields to the fields they are joined to? Access does not seem to be smart enough to figure this out itsself and I am not sure how to tell it to create the new record using the fields that it is related to.

This is very difficult to explain, any help would be greatly appreciated. I will be glad to post further clarification if I did a bad job explaining what I am trying to do.

Thanks,
Rob

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved