Questions tagged [ms-access]
Microsoft Access is an application development and database development tool from Microsoft. It combines the Microsoft Jet/ACE Database Engine with a graphical user interface and software-development tools. Other database engines, such as SQL Server and MySQL, can also be used as a database server for Access applications.
673
questions
0
votes
0
answers
9
views
How to add a calculated field to a Microsoft Access query that equals the minimum value of the previous column field
My current query returns something like:
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 the same query, or at worst a second query that uses this query to return
2/1/...
1
vote
1
answer
22
views
How to store decimal numbers in MS Access
I tried to store decimal number, but couldn't. It's Office 2019. I tried both number types, and different "format". One format had some zero's show up after a comma, but could not enter ...
0
votes
1
answer
21
views
How to print category tree in MS Access
I have a table for competences. I have made it like a parent-child relation, so there is for example driver-licence as a row, and car-driver-licence as another row, having 'driver-licence' as a parent....
0
votes
1
answer
21
views
Process for loading data into a logically-arranged Access database with many-to-many relationships
I'm struggling with the logic and feasibility of a certain workflow. I have a list in Excel containing DatabaseName, TableName, FieldName, SubtypeName, and DomainName. I'd like to select all of the ...
- 1
0
votes
0
answers
11
views
Update query based on multiple rows
I'm looking to build an update query in access based on multiple rows within the same table.
so select all records where the employee ID is the same, im using this at the moment
WHERE (((MasterRecord.[...
- 1
0
votes
0
answers
19
views
MS Access backend database deleted data restored on its own
I'm not sure if this is a MS Access or SharePoint issue, I have a MS Access backend database sitting on SharePoint drive and the database stores some test data. As I approaching the end of the test, I ...
- 11
0
votes
0
answers
34
views
Automatically fix case usage in table and column names from Access to MariaDB
I am converting an existing MS Access application to use a MariaDB backend running on CentOS 7. I used a tool from BullZip to convert the Access database to MySQL.
After that I used another tool to ...
0
votes
1
answer
45
views
In MS Access 2019, how do I find numbers in a lottery table that appear often together?
I have a table with 256 lottery drawings, pick 5, along with the drawing date.
I want to know how many times my favorite numbers (4,7,47) or any numbers, came out at the same drawing.
I am thinking
...
0
votes
1
answer
53
views
How to send an access database
I’m having trouble trying to find a way to send a ms access application to someone I have built one for. They say you cannot open it from OneDrive or problems occur. But is the person able to copy it ...
- 1
0
votes
0
answers
38
views
Migrate Access mde app and Microsoft Sql Database to another PC
I need to move an old Access app, which runs on a windows server 2003, to my Windows 10 PC,
The app is run from the Server desktop via mde file and is connected to a microsoft SQLEXPRESS database ...
- 1
0
votes
1
answer
226
views
Data Type mismatch error when using function in SQL Statement
I have a function which gives Levenshtein Distance used for fuzzy search of a field.
Option Compare Database
' Levenshtein3 tweaked for UTLIMATE speed and CORRECT results
' Solution based on Longs
' ...
0
votes
0
answers
90
views
Create a student-grade query from a relational database
I have three tables:
Students (Id, name)
Assessments (id, name, max_score)
StudentAssessment (Id, student_ID, assessment_id, score) where ids are FK from the other two tables.
I think this is the most ...
- 103
0
votes
1
answer
378
views
Access Search Form is not including null values
Sorry if this is an overly easy question, I don't use access often but was asked to make a search form for a table our company uses to track remake parts. I have a form created with a series of ...
0
votes
1
answer
34
views
Select employees who have NOT completed all training
I'm not a frequent DBA, but I'm working on something to track training complete for some employees and I'm a bit stuck. I'm using MICROSOFT ACCESS and have 3 tables:
Employees
Last_Name (...
- 3
0
votes
1
answer
160
views
Looking for file-based DB engine alternative to MS Access
My company has been chained to this MS Access '97 .MDB format we've had since the dawn of time. Our schema is hierarchical, with multiple one-to-many relationships. We are at a point where we are ...
- 3
0
votes
0
answers
152
views
Is this a good database design for storing recipes that contain "sub-recipes"?
To preface, I haven't designed or worked directly on a database in years, and my general SQL knowledge is still fairly limited.
I'm designing a recipe database in Access to store interesting recipes I ...
- 101
0
votes
0
answers
11
views
Using OR for separate fields in Access' Advanced Filter/Sort
I want to create an advanced filter on an Access table that involves several criteria, including an OR between two fields, e.g. "fld1 > A AND (fld2=B OR fld3=C) AND fld4 <= D".
I have ...
- 101
5
votes
3
answers
1k
views
SQL Database vs Multiple Microsoft Access Databases
The company I work for produces a lot of test data in raw text files from lots of different devices. Each device belongs to a 'process' of which there are only a few dozen. Currently we have a program ...
1
vote
0
answers
241
views
MS Access SQL - Left Join (or sub-query?) most recent related record
Hello Database Administrators.
Thank you so much for your help with this issue. It blows my mind that something so simple can be so complicated.
I have an MS Access database with two tables.
The first ...
- 11
0
votes
0
answers
28
views
MS ACCESS 2k3 Flatten out a number of like records
Sorry never seem to understand how to do this kind of query in SQL
Given the following data, I am looking to make one or more Queries compatible with MS ACCESS 2k3 that will provide the following ...
- 1
1
vote
0
answers
47
views
Best Method for Modeling/Implementing Dependent Primary Child?
I’ve implemented Primary Child functionality using a third table as described by @ypercube here (and other posts). I chose this method because it’s database-agnostic, simple (no triggers or deferred ...
- 11
0
votes
0
answers
27
views
Records in table not showing in MS Access
I created database system using VB6 and MS Access database. It has been installed many computers and working fine. But I have a problem. Though can retrieve the data from VB system, when open the ...
0
votes
0
answers
143
views
MS Access and the Dataverse -- lost primary keys and read-only queries
I'm trying to create a database application with tables stored on a Dataverse/Teams backend and an MS Access front-end. I have a simple one-to-many database design, and a form with a query as its ...
- 3
0
votes
0
answers
10
views
Trying to input table-format data using Access Data Input form
We're trying to format and input some scientific data into an Access database, and unfortunately the source of the data is in Excel files with very inconsistent formatting, so we can't use a blanket ...
0
votes
0
answers
130
views
Can't view/create System DSN in windows 10 using ACEODBC.DLL
I need to create a system dsn to connect to a Microsoft access accdb for my fedex shipping station.
I can seemingly create a dsn and everything seems fine but then it doesn't show up in the list. And ...
- 13
0
votes
1
answer
607
views
MS Access - How to sum matching criteria from two columns in same table
I have an MS Access database with four columns of interest that I am trying to work with.
The rows represent sold jobs, and the columns of interest are "OriginalSaleAmount", "...
0
votes
0
answers
294
views
Filter Combo Box list without filtering Recordset - MS Access
I am trying to create a form where users select related pieces of equipment that are already in the database. Since they are existing I want to use a combobox with populated list of existing entries.
...
0
votes
0
answers
28
views
How to handle user input of priority?
I've been learning MS Access 2016 the hard way (trial/error + search everything), and I've hit something that's got me completely stumped. This one is a bit of a two-fer.
I have a database that ...
0
votes
0
answers
22
views
How can I GROUP BY based on time between records, rather than time 'buckets'?
I've been learning MS Access 2016 the hard way (trial/error + search everything), and I've hit something that's got me stumped.
TL;DR: How do I re-bundle my dataset of operations into transactions? A ...
0
votes
1
answer
883
views
Combine multiple rows into one single column (MS Access)
I am trying to build a query from a table (Tbl_XYZ) that will give me the information for the primary key all in one row rather then creating dups for that primary key and showing up with multiple ...
- 1
0
votes
0
answers
16
views
Importing a database with the exact same schema as the destination database
I am working in Access with a fairly simply relational database. I want to import another database of the EXACT same schema, but when I use Access' import tool the software creates extra tables (...
- 1
0
votes
1
answer
392
views
SQL in Access to add leading 0 in front of zip codes
for a project I am working with a table of zip-codes that I imported from a *.csv file, exported form Excel. Those zipcodes all have 5 numbers and are formatted as text within my database. Some of ...
- 1
0
votes
1
answer
78
views
MS Access and multiple users
I'm looking for some help regarding Microsoft Access and database design.
I have been developing a database application on Microsoft Access for use in my organisation. I work in a hospital and we have ...
- 3
0
votes
1
answer
34
views
Inputting same data on identical Field Name on different table
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 - "...
- 1
0
votes
1
answer
231
views
VBA - Hide a Form tab depending on the value of a field
pretty simple question here in scope.
Question:
Wondering If I would be able to hide the tabs of a form based off the values of a table's fields.
I have been reading the 2019 Access Bible and so far ...
0
votes
1
answer
14
views
What kind of object is made when we make CREATE VIEW, and what sort of commands can be ran on it?
I would like to understand the difference between a table and a View in SQL (Ms Access, if it makes any difference). So I know Create View command can be used to "pull" all fields and ...
- 11
1
vote
0
answers
242
views
Select value and display in a combobox if the datetimepicker value lies between two extreme dates in a table in database
I am creating an attendance management system in vb.net and using MS access as my database. I created a leave form in which the student on leave has a roll number and his details along with the from ...
- 11
-1
votes
2
answers
86
views
Database schema - Set Limit values in correlation to groups and/or it's members
Provided there are pre-existing tables (diagram attached below). They each have numerous use-case as far as potentially related attributes. Still, I am at a roadblock following best practices with ...
0
votes
1
answer
82
views
Compact Database VBA for an .mdb that utilizes a .mdw security file
The below code works great when you call an unsecured database from another database. But I am trying to compact an .mdb in Office 2013 that has user-level security (.mdw).
If I try the below code ...
- 1
0
votes
0
answers
24
views
How do you modify query to include additional fields in the results?
I have the following MS Access query:
SELECT *
FROM (SELECT [cnPart],[desc] as DATA,"desc" as CAT from PartData
UNION SELECT [cnPart], [qty], "qty" FROM PartData
UNION SELECT [...
- 1
1
vote
1
answer
515
views
Find Max Date from a Distinct Row Then Add Value to a Table Column
Quick disclaimer - I am familiar with Access, but a novice at MySql within Access.
I have a table with activity data that I pulled into a query to filter down to only necessary data. The query table ...
1
vote
2
answers
36
views
Return results with several LIKE and NOT LIKE requirements
I am trying to create a query that selects only a set of values, but excludes other ones.
The column I am selecting it's called "Participants" and the values therein are not a single text ...
- 13
0
votes
1
answer
141
views
How to Stop MS Access From Undesired Cascading Delete? [closed]
When I delete a record from one Microsoft Access table, it deletes the corresponding record in another table. I need to stop that.
Table 'items' has ID (the key), item_name, and some other fields.
...
0
votes
1
answer
79
views
Q: Updating multi table linked view with MS Access
I have an MS Access db with a linked table pointing to an MS SQL server database view.
We have been using MS Access as a simple front end to change data contents via this view.
The view was ...
0
votes
1
answer
341
views
Expressing A Composite Key in a Data Dictionary
I am looking for an example of expressing a "Composite Keys" in a data dictionary.
I am using an MSO Access application with a Lecturer Table and Subjects Table where I have taken the ...
- 1
1
vote
1
answer
131
views
How to design a query to pop up input with two text boxes in MS Access
I ask this question while I could not find any similar question neither on Google nor DBA Stackexchange, this question might be duplicated.
I wrote this query in MS access to check for first name and ...
- 11
0
votes
1
answer
43
views
Consolidate / sort / combine / merge data for dummies
I am looking for a non-scripting solution to combine data from 3 different tables (preferably using spreadsheet software like Microsoft Excel but a database would also work e.g. Microsoft Access). I ...
- 103
0
votes
0
answers
19
views
how to Manipulate duplicated records using access
Using Microsoft Access, I have a number of records that have duplicated field-1. I need to form a query such that the duplicate records get unique ; BUT the next filed-2 data must be seen in columns ...
- 1
1
vote
1
answer
229
views
3 Simple Queries SQL Student Question, Query Errors in Microsoft Access
The First Question is: "List the name of each trip that does not start in New Hampshire (NH)."
SELECT TripName
FROM Trip
WHERE State!='NH'; /* works with State='NH' */
The Second Question ...
- 135
0
votes
1
answer
35
views
query for showing columns from various tables, grouping and showing calculated field in each row of the result
I am doing a stock management system.
In one table I store items:
ITEM<br>
id_item<br>
item_name<br>
ORDER<br>
id_order<br>
date<br>
ORDER_ITEM<br>
...
- 135