New answers tagged

0

Thanks for the info! fred & WGLJ. The option of using SQL to make the change works - add the sql commands to a new query's sql view and run. There is no feedback of what happened, but when you open the table, you'll see.


1

Where My_ID = ID0001, ID0002, ID0003… IIF(Val(Right(DMax("[My_ID]","[MyTableName]"),4))+1<10,"ID000" & Val(Right(DMax("[My_ID]","[MyTableName]"),4))+1, IIF(Val(Right(DMax("[My_ID]","[MyTableName]"),4))+1<100,"ID00" & Val(Right(DMax("[My_ID]","[MyTableName]"),4))+1, IIF(Val(Right(DMax("[My_ID]","[MyTableName]"),4))+1<1000,"ID0" & Val(...


0

There is a bug in Access 2016. The MS Access team is aware of it, and is working on a fix for it. No ETA yet on when it will be available. It involves the Datatype dropdown for Numbers. Only the default datatype is available. If you need to change or add number fields, the work around is to go to Options for Object Designers and change the Default ...


0

As I stated in a comment above, you'll want to re-enable a primary key. If you already have a filled table, you may have to migrate it to a new table, as - from what I recall - Access doesn't like creating autonumber fields in existing tables that don't currently have them. So, create a new Query, ignore the design view and click over to SQL view (close ...


5

The strongest advice I can give is to keep the primary key immutable unless it is absolutely unfeasible for your use-case. You appear to be describing 2 different datasets. User-Actions taken during a given Period ( defined by the composite key of Year-Week ) User-Actions taken during an unknown Period My initial impression is that the unknown period ...


1

You can use a default date of something way out of bounds. e.g. in the table definition YEAR INTEGER DEFAULT 1776, WEEK INTEGER DEFAULT 99 That way you know that everything gets entered with some date. It will continue to not allow you to enter in duplicate data. In addition, you can create an exception report based off these values kicked off to someone ...


0

You could do a lot of stuff but integer and text do not sort the same. Text would break a search on week > 6. In text 10, 11, 12 are not > 6. I would use 0 for no date Not that size is the big of a deal but you would use tinyint for week and smallint for year one varchar is the size of a smallint


4

What you appear to have is two tables that refer to a common field They have an inferred relationship In the case of a common field then create a master table for Field1 that is nothing but the valid values for Field1. Table1 and Table2 will each have references to the table Field1. If you really need a direct relationship from Table1 to Table2 ...


7

What you describe is not a one-to-many relationship but a many-to-many relationship because the column Field1 is not unique in any of the two tables (and please pick better names to describe the columns and tables, "Table1", "Table2", "Field1" say nothing about the table or the column and are very confusing). I see two options depending on what your ...


0

It is helpful if you post the table design, or at least what you will be joining on. I created a sample that has 2 tables: 1) Student - Name (PK) 2) Training - Id (PK, Autonumber), Name, Class, Date Create a relationship between Student.Name, Training.Name, enforce referential integrity. There is a nice "Find Unmatched Records" query wizard in access ...


0

I'm not real familiar with Access syntax, but I think the general idea you're after would be something like this... Instead of filtering for only records that don't meet your requirements, add a calculation to your select list that indicates if the record/ruleset combination resulted in a pass or fail. Then group by your other selected columns, and and look ...


-1

SQL is a special-purpose programming language designed for managing data in a relational database, and is used by a huge number of apps and organizations. Watch this Video to learn basics of SQL.


1

It looks like that at the time I'm writing this there is no way to pass Windows security credentials when using a SQL Server user. The SQL Server user will always use the credentials of the Windows account the service is running with. In most cases it's not even an actual user as it's just a service account that can't be bound to a domain controller to ...



Top 50 recent answers are included