I'm trying to use Microsoft Access to start an employee database. However, I want the display of the Employee ID to be the initials and then their number. For example, John Smith with the ID 0002 is JS0002. I've been playing around with format but it doesn't seem I can do this if I put my EmployeeID as an autonumber. Thanks in advance!

currently i am new in ms access, And currently - as a trial and learning step - I created Two tables, and a Single Form that can be used to put a value to both tables, those are: **Table 1** - "Data 1", the Fields are: ID, RegDates, CatName1, CatName2 **Table 2** - "Data 2" ID, RegDates, DogName1, DogName2 **Form 1** - "Input Form" ID Registration Dates CatName1 CatName2 DogName1 DogName2 I can't populate ID on both tables using Relationships, but I cant populate nor filling RegDate fields on both Tables using one single "Registration Date" Form on Input Form How can I achieve filling both Field Name - which is RegDate - using only one single Input textbox on "Input Form"? What I want is, If I entry the Registration Date on "Input Form" Both regDate field on Data 1 and Data 2 are filled Thanks!

Is there a PostgreSQL query or command that returns the field names and field types of a query, table or view? E.g., a solution if applied to simple SELECT query like SELECT * from person should return a list like: Column Name | Column Type =========================== First Name | character Last Name | character Age | integer Date of Birth | date I have looked up the information_schema views described in an answer below and it seems to cover tables quite well, and I suspect it covers views as well but I haven't checked that yet. The last is any arbitrary but valid SELECT query eg involving, JOINS, UNIONS etc, on the database. Is there a built-in procedure, or other stored procedure or script that can return the same for any valid QUERY at all? I am developing a program that creates data and querying forms and the information is needed for data validation and executing functions on the returned data.

What's the best practices with a date field when you want to fill in the fact that it's not filled in and not leave it NULL? should you put 1999-01-01, 9999-09-09, 1890-12-31...? Are there any rules for this?

I am a complete novice, I hope my terminology is correct. I have a database created long ago. It helps keep track of visitors. The visitor’s data is entered manually, but some fields are calculated and most cannot be empty. There is a field (“group2”) which is unused. I want to start using it. I want it to be pre-filled in by default with “Y”,but be able to change it to “N” (and no other characters and not empty). In my research, it looks like the ENUM function is usable to limit input; I wonder if I am on the right track, and if so, how do you set the pre-filled default to Y?

I have a simple query on a very complex query that reads:

lang-sql
SELECT Query1.Rdate, Query1.Rank, Query1.Score
FROM Query1;

It returns: | RDate | Rank | Score | | ----- | ---- | ----- | |2/1/2022|5|0.9| |2/1/2022|3|0.6| |2/1/2022|1|0.3| |2/1/2022|2|0.8| |2/1/2022|4|0.2| I need a second query that uses this query to return: | RDate | Rank | Score | MinScore | | ----- | ---- | ----- | -------- | |2/1/2022|5|0.9|0.2| |2/1/2022|3|0.6|0.2| |2/1/2022|1|0.3|0.2| |2/1/2022|2|0.8|0.2| |2/1/2022|4|0.2|0.2| where the new column of 0.2 represents the Minimum value of the previous column. I know I can write a query that will return a single column of the value 0.2 and then ANOTHER QUERY to combine that column with my original query. But I have over 200 original queries. I would then end up with over 600 queries. I don't want to go down that path if I can avoid it.

I understand how ORDER BY clause works and how the FIELD() function works. What i want to understand is how the both of them work together to sort. How are the rows retrieved and how is the sort order derived +----+---------+ | id | name | +----+---------+ | 1 | stan | | 2 | kyle | | 3 | kenny | | 4 | cartman | +----+---------+ SELECT * FROM mytable WHERE id IN (3,2,1,4) ORDER BY FIELD(id,3,2,1,4) The query above will result in +----+---------+ | id | name | +----+---------+ | 3 | kenny | | 2 | kyle | | 1 | stan | | 4 | cartman | +----+---------+ something similar to saying ORDER BY 3, 2, 1, 4 QUESTIONS - How does this work internally? - How does MySQL get the rows, and calculate the sort order ? - How does MySQL know it has to sort by the id column ?

I have the following code : start transaction; show datestyle; set datestyle to DMY; show datestyle; select concat(timestamp_field, '') from my_table ; commit; It outputs this : DateStyle ----------- ISO, DMY (1 ligne) DateStyle (after set datestyle to DMY;) ----------- ISO, DMY (1 ligne) select concat(timestamp_field, '') from my_table ; concat --------------------- 2004-09-01 00:00:00 (1 lignes) My datestyle change doesn't seem to be accepted. Moreover, concat keeps using ISO to format my timestamp_field. I would expect something like this : concat --------------------- 01/09/2004 00:00:00 (1 lignes) >*NOTA:* I don't want to use to_char function here. I want to understand how concat automagically converts a timestamp/date field.

I have a table with ten fields, f1 (being an integer field) to f10 (being a character field). In between there are other integer, character and date fields. I would like to copy those field definitions to another table, without needing to re-write the whole definitions. I'm working with the Data Dictionary, who seems not to cover this feature. **Edit after first answer** The first answer mentions how to do this at runtime, but I'm specifically looking for a solution at design-time. **Edit** I've found there are different commandline utilities which can be used for working with Progress database: ProDB for creating a new database, ProUtil for doing several tasks (but I don't find a ProUtil ). How can I achieve this?

I have an influx database with one tag key (called machine) and multiple field key (called cpu and memory). I want to be able to find all unique tag values for each field key or filled field keys for a given tag value. Here is an example of database:

SELECT * FROM "myMeasurement"

name: myMeasurement
-------------------
time                   machine     cpu     memory
2020-01-01T00:00:00Z   m1          51
2020-01-01T01:00:00Z   m2                  2048
2020-01-01T00:00:00Z   m1          52
2020-01-01T01:00:00Z   m2                  2054

I would like to retrieve **m1** when requesting machine containing **cpu** values and/or **cpu** when requesting not empty field keys for machine **m1**. Thanks

I have a table in postgres with 10 fields, and need to check for certain values in 3 of them, then return the field name which met the condition and the number of rows in each field. So it's something like:

select count(*)
from table
where field1 ilike '%E+%'
OR field2 ilike '%E+%'
OR field3 ilike '%E+%'

and then return the field which met the condition AND the number of rows (which I'm not sure how to do it)

Any field that I would expect a value (Like an orderNumber or customerNumber) I would set as not nullable. If I'm expecting the value of the field to be not null, shouldn't I also always have a constraint that does not allow for zero length strings or space only strings?

I have a requirement where I want to fetch and display all the fields with NULL value for a particular row in the table. Some thing like: select 'all columns with NULL' from table_xyz where primary_key='pk_row2'; I use SQL Server 2012. Please help achieve this.

I feel kind of embarrassed here, I've always used the terms "column" and "field" completely interchangeably, which recently caused some confusion in a technical discussion. I was told, though, that this wasn't correct, that it should be (translating each term into spreadsheet terminology, ignoring data types and all the other stuff that make databases useful): * Database Column: like a spreadsheet column * Database Record: like a spreadsheet row * Database Field: like a spreadsheet "cell" (a specific column of a specific row) Is this right? I could have sworn that column and field are used more interchangeably than that. I certainly have been. So we *don't* add *fields* to a table, we add *columns* to a table, and fields are only relevant when talking about data within a record? Other thoughts on column vs field? Edit: to clarify, the current context is MS SQL Server. My background before SQL server was MS Access, which might influence my use of these terms.

How is tsrange stored in binary? For example create table CREATE TABLE public.test (t tsrange); INSERT INTO test VALUES ('[2010-01-01 14:30, 2010-01-01 15:30)'); INSERT INTO test VALUES ('[2011-01-01 14:31, 2015-11-01 15:30)'); INSERT INTO test VALUES ('[2017-01-01 14:31, 2018-11-01 15:30)'); COPY test TO '/tmp/pgcopy' WITH (FORMAT binary); COPY test TO '/tmp/pgcopy.csv' WITH (FORMAT csv); It outputs: cat /tmp/pgcopy.csv "[""2010-01-01 14:30:00"",""2010-01-01 15:30:00"")" "[""2011-01-01 14:31:00"",""2015-11-01 15:30:00"")" "[""2017-01-01 14:31:00"",""2018-11-01 15:30:00"")" hexdump -C /tmp/pgcopy 00000000 50 47 43 4f 50 59 0a ff 0d 0a 00 00 00 00 00 00 |PGCOPY..........| 00000010 00 00 00 00 01 00 00 00 19 02 00 00 00 08 00 01 |................| 00000020 1f 19 f9 a9 aa 00 00 00 00 08 00 01 1f 1a d0 3d |...............=| 00000030 4e 00 00 01 00 00 00 19 02 00 00 00 08 00 01 3b |N..............;| 00000040 c8 89 51 11 00 00 00 00 08 00 01 c6 7b 1a 3a 0e |..Q.........{.:.| 00000050 00 00 01 00 00 00 19 02 00 00 00 08 00 01 e8 08 |................| 00000060 0d 77 11 00 00 00 00 08 00 02 1c 9a dc 4d 0e 00 |.w...........M..| 00000070 ff ff |..| 00000072 One field is: 00 00 00 19 02 00 00 00 08 00 01 e8 08 0d 77 11 00 00 00 00 08 00 02 1c 9a dc 4d 0e 00 There: 00000019 - is 25 bytes length 02 - brackets 00000008 - subfield length 0001e808 0d771100 and 00021c9a dc4d0e00 - stored timestamp with miroseconds. How to convert it to integer timestamp?

I need to build an audit report to identify field changes between two tables, as well as between consecutive rows in one of the tables. The first table (data) holds the current data: id data_id field1 field2 data_dttm 1 100 data3 data3 2017-05-03 00:00:00.000 CREATE TABLE [dbo].[data]( [id] [bigint] NULL, [data_id] [nchar](10) NULL, [field1] [nchar](10) NULL, [field2] [nchar](10) NULL, [adt_dttm] [datetime] NULL ) Insert INTO data Values (1, 100, 'data3', 'data3', '2017-05-03 00:00:00') The second table (data_hst) holds data prior to it changing (trigger type operation). data_hst_id data_id field1 field2 chng_fld_txt data_dttm 1 100 data1 data2 field1|field2 2017-05-01 00:00:00.000 2 100 data2 data3 field1 2017-05-02 00:00:00.000 CREATE TABLE [dbo].[data_hst]( [data_hst_id] [bigint] NULL, [data_id] [bigint] NULL, [field1] [nvarchar](200) NULL, [field2] [nvarchar](200) NULL, [chng_fld_txt] [nvarchar](200) NULL, [data_dttm] [datetime] NULL ) Insert INTO data_hst Values (1, 100, 'data1', 'data2', 'field1|field2', '2017-05-01 00:00:00'), (2, 100, 'data2', 'data3', 'field1', '2017-05-02 00:00:00') The 'chng_fld_txt' field holds the list of fields that were modified, pipe delimited. I need to identify what has changed between the data table row and the most recent data_hst table row, as well as the change between the consecutive rows in the data_hst table. An audit report tracking each change identifying the old and new value as they occurred. The result like this: table_name db_field old_value new_value data_dttm data field1 data1 data2 5/1/2017 data field2 data2 data3 5/1/2017 data field1 data2 data3 5/2/2017 I have a convoluted bit of dynamic sql with a cursor that works for the first condition, but not both. Hoping there's a cleaner way to satisfy both conditions. -- DROP TABLE #changed CREATE TABLE #changed( [tbl_hst_id] [bigint] NULL, [change_field] [nvarchar](200) NULL ) ON [PRIMARY] DECLARE @db VARCHAR(200) SET @db = 'data' DECLARE @change_date DATETIME DECLARE @change_date_varchar NVARCHAR(200) SET @change_date = GETDATE() SET @change_date_varchar = LEFT(CONVERT(VARCHAR, @change_date, 120), 10) DECLARE @changed_table nvarchar(max) SELECT @changed_table = 'SELECT TOP 2 t1.' + @db + '_hst_id as tbl_hst_id, t1.change_field FROM ( SELECT A.' + @db + '_hst_id ,Split.a.value(''.'', ''VARCHAR(100)'') AS change_field FROM ( SELECT ' + @db + '_hst_id ,CAST('''' + REPLACE(upsrt_chng_fld_txt, '','', '''') + '''' AS XML) AS String ,adt_dttm FROM ' + @db + '_hst ) AS A CROSS APPLY String.nodes(''/M'') AS Split(a) WHERE adt_dttm >= DATEADD(D,-4,''' + @change_date_varchar + ''') ) T1 WHERE T1.change_field NOT IN ( ''upsrt_dttm'' ,''upsrt_trnsctn_nmbr'' )' exec ('insert #changed ' + @changed_table) DECLARE @delta_field VARCHAR(max) DECLARE @db_sql_all NVARCHAR(max) = '' DECLARE @getDeltaField CURSOR SET @getDeltaField = CURSOR FOR SELECT change_field FROM #changed OPEN @getDeltaField FETCH NEXT FROM @getDeltaField INTO @delta_field WHILE @@FETCH_STATUS = 0 BEGIN DECLARE @db_sql NVARCHAR(MAX) SELECT @db_sql = 'select ''' + @db + ''' as table_name , ''1'' as id ,''' + @delta_field + ''' as db_field ,CAST(hst.' + @delta_field + ' AS NVARCHAR(200)) as old_value ,CAST(c.' + @delta_field + ' AS NVARCHAR(200)) as new_value --,c.upsrt_usr_id as change_by --,c.upsrt_dttm as change_time from ' + @db + ' c inner join ' + @db + '_hst hst on c.' + @db + '_id = hst.' + @db + '_id join #changed ch on hst.' + @db + '_hst_id = ch.tbl_hst_id UNION ' SET @db_sql_all = @db_sql_all + @db_sql FETCH NEXT FROM @getDeltaField INTO @delta_field END CLOSE @getDeltaField DEALLOCATE @getDeltaField IF len(@db_sql_all) > 0 BEGIN SET @db_sql_all = SUBSTRING(@db_sql_all, 1, len(@db_sql_all) - 6) END PRINT(@db_sql_all); EXEC (@db_sql_all);

Due to limitations in my GIS software, I need to write select queries for each of my 200+ tables in my database. The queries need to select all columns, except for one, the SHAPE column. Is there an efficient way to get all the field names for each table - for the purpose of writing select queries? What I've tried: 1. I think this is possible with SQL Developer(free). But unfortunately, I don't have SQL Developer, due to I.T. challenges. 2. I'm aware that when I create a SELECT * view on a table in Oracle, the query is translated to explicitly select individual fields. So I could a) create a SELECT * view for each table, b) get the view definition, and c) write the query for each table. This is probably quicker than manually typing each field name, but not by a lot. How can I efficiently write select queries for tables with lots of columns?

I would like to list the default values for all columns in all tables in an Oracle database. The objective is to review the default values, and find any that need updating. There are hundreds of tables in the database with columns that have default values, so it would be preferable to do this programmatically, rather than manually. How can I do this?

I am designing a ROAD_INSPECTION table that has a CONDITION field: +---------+-----------+ | ROAD_ID | CONDITION | +---------+-----------+ | 1 | 2 | | 2 | 5 | | 3 | 1 | | 4 | 1 | | 5 | 4 | +---------+-----------+ Are there any standards or conventions that suggest how to format ratings as ranges? Example, what's the best format for the CONDITION rating? - 0 to 5, zero is best - 0 to 5, five is best - 1 to 5, one is best - 1 to 5, five is best -------------------------

I have two tables in a database, folowwing this model: scientists : id (int) | name (varchar) | surname (varchar) | publications (int) publications : id (int) | fulltext (varchar) I would like to add a field to the publications table which refers to scientists' id, but I don't know how many scientists will be reffered to (can be 0, 1 or any other positive number) in order to ease the count of publications for each scientist. What are my options ? Is my approach wrong ?