0

We use software connected to a database that has certain requirements for records in the table assay, however, our database is larger than just what this software uses and stores all data relating to assays in the table sample.

CREATE TABLE `sample` (
  `sample_id` varchar(255) PRIMARY KEY,
  `depth_from` real,
  `depth_to` real,
  `hole_id` varchars,
  `bag_id` varchar(255),
  `type` varchar(255),
  `weight` real,
);

Samples of sample.type 'rock chip' have a sample.depth_from value while samples of sample.type 'crm' do not. The software uses the subset 'rock chip' to display a downhole log. i.e. I need to use a subset of table sample.

SELECT *
FROM sample
WHERE sample.type="rock chip"
;

Too much detail?

Here is the issue; since .accdb was introduced I cannot connect directly to a query, I assume because of the updated data types. The software was originally designed to do this because this is a very common kind of dataset. So, aside from being very annoyed, I am also trying to use derived tables to get around this. But, how do I write back to the original table? With a query, changes and new records would be written to the sample table. I see assay as a temporary thing so changes can't go there. I see problems arising.

Also, the new table query has to be run manually and that is definitely going to be an issue. Users can't be messing with queries.

SELECT * INTO assay
FROM sample
WHERE sample.type="rock chip"
;

How would you go about making a table temporarily and making sure it can be written back to the original table? Is there a way to make this happen automatically?

And in MS Access.

Elizabeth
  • 61
  • 1
  • 10

1 Answers1

1

It sounds like you want a view:

CREATE VIEW assay AS
    SELECT *
    FROM sample
    WHERE sample.type = "rock chip";

This is evaluated when the user uses the view. So, it is always up-to-date.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • "The Microsoft Access database engine does not support the use of CREATE VIEW, or any of the DDL statements, with non-Microsoft Access database engine databases." – Elizabeth Mar 21 '21 at 01:18
  • The only things that I might guess is the semicolon at the end. However, this needs to be done directly in the database: https://learn.microsoft.com/en-us/office/client-developer/access/desktop-database-reference/create-view-statement-microsoft-access-sql. Although presumably you are using MS Access as the database. – Gordon Linoff Mar 21 '21 at 01:18
  • https://stackoverflow.com/questions/6382064/ensure-sql-92-on-access-2000-mdb-to-sql-server-under-access-2010 – Elizabeth Mar 21 '21 at 01:35
  • It just creates a query. Sigh. Not going to work – Elizabeth Mar 21 '21 at 01:35
  • I don't get the point of CREATE VIEW if it just creates a query. – Elizabeth Mar 21 '21 at 07:38
  • @Elizabeth . . . It created a *named* query that looks like a table. It is not "just a query". – Gordon Linoff Mar 21 '21 at 12:39
  • I am unsure of the difference. It literally puts the SELECT query into a query with the name I gave it. And it is exactly like any other query I have created. – Elizabeth Mar 22 '21 at 14:21