Questions tagged [ms-access]

Microsoft Access is a relational database management system that is part of the Microsoft Office suite.

Filter by
Sorted by
Tagged with
0
votes
0answers
7 views

Microsoft Access question

In my I.T class I have to make a program in Microsoft access. I want to make a system that exports inputted data into an already prefilled word document, that adds in the inputted data. Basically it ...
0
votes
1answer
13 views

Single Column index or 3 column index in MS Access

I am creating a table with a single memo field and either 3 text fields (tablename, fieldname, caseID) or a single text field with the 3 text strings concatenated. There will be a single NO ...
0
votes
1answer
20 views

How can I import multiple .xlsx and .xls files into multiple tables in Access?

I have 100 .xslx and .xls files with different formats and I need to import them all separately into Access keeping their original file names intact. Folder example: C:\Users\username\Desktop\...
0
votes
0answers
11 views

MS access >2gb and inaccessible: Can't be repaired followed by 'Object invalid or no longer set'

We have added 2 queries that should drop and load tables into our DB. For an (at this time) untracable 'mistake', it is likely that the queries only loaded and did not drop any tables to end up with a ...
0
votes
0answers
19 views

How do I set up a report that shows number of clients by age group when their age is stored by date of birth in Microsoft Access

I am working on setting up a new database in Microsoft Access for a food locker it will be used for client input as well as tracking client visits. One of the monthly reports they need is how many ...
0
votes
1answer
24 views

Manipulating duplicate records in access

I have a table with data as follows: Field1 Field2 Field3 10 10 20 10 20 10 30 1 0 20 1 1 20 1 20 1 30 2 2 30 2 30 2 My goal is to ...
0
votes
0answers
12 views

How to open a form at a specific record, retrieved from a query or table [closed]

I have an MS Access 2016 database that users primarily interact with via a Form (with its own subforms), but there are several Queries that summarise key records in the database, that regularly need ...
0
votes
0answers
33 views

ODBC in Access working but not in SQL Server

I have a postgres database running on a remote server. I'd like to be able to query it in SQL Server. I downloaded the ODBC Driver for postgres and created a system DSN which successfully runs a ...
0
votes
0answers
12 views

SSMA for Access - Migration Assistant Wizard remove password prompt after linking table

Hello used SSMA to create a linked table using SQL. Is there a way to remove this password prompt after opening the table in Access ? I tried adding the username and password in the connection string ...
0
votes
0answers
16 views

OPENROWSET link from Access to Excel

I am trying to use Microsoft Office 12.0 Access Database Engine OLE DB provider to query an XLSX file with OPENROWSET. An attempt to query just spins in SSMS without error, failure, or a result set. ...
0
votes
1answer
61 views

how to find min max of sum?

hello everyone i try to find minimum and maximum value of SUM(units_sold) i test some ways but i can't find best solution please help me ! SELECT TOP 1 dp.product_name, SUM(fs.units_sold) AS ...
-1
votes
3answers
49 views

Query for showing missing years

I have a question about showing the missing values by a query. Given table named Top2000 with fields Artist, Title, Ranking, Year, the following query... Select * from Top2000 order by Artist, ...
0
votes
0answers
23 views

How can you effectively visualise a complex relational database?

I have an Access database which contains all the data from an open-source Japanese dictionary, JMdict. The database contains 24 linked tables, so reading it is complicated. The reason for this is that ...
0
votes
0answers
27 views

MS Access front-end cannot modify records when connected to MySQL

I migrated my Access tables to MySQL hosted server and linked the tables back to MS Access front-end and now I cannot edit the records in my forms. I can see the data and go between records, but I ...
0
votes
1answer
37 views

ms-access concat a string with control source

Sorry for this likely noob question but I cannot seem to find the solution. I'm trying to modify an existing field on a report, standard in this field the data is being pulled from the control source ...
0
votes
2answers
26 views

Best way to enter data in DB that is spread out date wise

I have an Excel file that, to keep it simple here, has 3 columns: ActivityID, ActivityName and TotalHours. TotalHours column is basically all the hours allotted to that specific ActivityID and the ...
1
vote
0answers
39 views

SSIS to import tables from Access DB to SQL

im trying to design an SSIS package to import 80 tables from Access DB into SQL DB, I have done it through the export and import wizard then saved the SSIS package but i didnt like the way the package ...
0
votes
0answers
21 views

Word file running an SQL command - How do I find the source SQL database?

A word 2013 file has a merge function that activates whenever this is open and it is "SELECT * FROM 'database'". As the final results have errors (the final word document feeds from the wrong field ...
0
votes
1answer
30 views

How to update a yes/no field to yes when all related fields in another table are marked yes

I'm new to database stuff (read: database idiot) and I am using Access. I have a table (let's call it "projects") that has a list of projects and one the fields (let's call it "actions_complete") for ...
0
votes
2answers
41 views

What's an alternative solution to using a null value for a foreign key field?

I have a few tables in my database which contain a Foreign Key field linking to another table. The problem is, the value for that field isn't always going to be present, and so sometimes I might have ...
0
votes
0answers
24 views

1 table with 2 same foreign keys to 3rd table with Primary Key

First time building a database in Access. I've been searching, but I did not find something to solve my question, so I finally decided to ask. It may be a really stupid question but what is the best ...
0
votes
2answers
41 views

How can I insert a record into a table that depends on a foreign key linking to a previous table?

Following on from this question. I have a dictionary file written in XML. I've written a program in VB.NET to parse this file and store all the data in Class objects, and now I want to write a ...
4
votes
2answers
1k views

What principles should I follow when entering data into a complex relational database?

I'm new to Databases, so forgive me if this is a basic question. I'm attempting to parse a complex open source dictionary, and output the data into an Access database, which is equally complex: I ...
0
votes
1answer
12 views

Is there any practical difference between using the Lookup Wizard to create a FK relationship, or using the Relationships window?

I'm very new to Access, so I'd appreciate patience if this is a basic topic. I'm creating a lot of tables which I need to link. I have two tables, Keyword and KeywordOrthography. KeywordOrthography ...
0
votes
1answer
42 views

Can't open Microsoft Access Database inside a Microsoft SQL Server Filetable Folder

We recently started to migrate the Data on our Fileserver to SQL Server (Version 14) Filetables and I encountered a problem with MS Access Databases (.accdb, Access 2007). When the .accdb file is ...
0
votes
1answer
53 views

how to professionally normalize my database - screenshots included

Hello database administrators. I got some excel spreadsheet with data of "my customers" and I'd like to create professionally designed database out of it. Can anyone tell me how this data should be ...
0
votes
1answer
21 views

Postgresql and MS Access unable to insert new data

I have a Postgresql server with a data base and table, columns dont matter. My issue is my primary key is set to BIGSERIAL, it auto-increments. However, on MS ACCESS when I create a form to insert new ...
0
votes
1answer
46 views

Generating an expiry date based on fields in different tables

I want a field that gives the date a certificate expires. I think i would create a query and add a field using something like this.. dateadd("d", [DaysToExpire], [DateCompleted]) My trouble is, [...
0
votes
1answer
65 views

See direct (instead of indirect) info about rented book in my “actors_directors” talbe dropdown list - Access365

I'm building a database of "actors_directors" who are renting "books" from my library - a kind of exercise to learn database basics :-) This is how my "actors_directors" table looks like. They are ...
1
vote
0answers
17 views

Modeling a system with optional components

I am working on an MS Access database that is intended to help keep track of a large scale implementation of many component parts connected together. I have created a working implementation based on ...
0
votes
0answers
19 views

Auto Increment Field

I am trying to implement a simple table in MS - ACCESS where im using two PK's for say a WEATHER table. PK LOCATION PK ID DATE TEMPERATURE I wish to increment the id based on the location, ...
0
votes
0answers
15 views

How to create a linked table to an SQL global temporary table

In my Access database VBA code, I would like to create a linked table to a global temporary table in SQL Server. I can't seem to get it to work. This is what I have tried: Dim adors as new ADODB....
0
votes
0answers
12 views

Return Value Corresponding to Latest Date (SQL)

Let's say that I have these tables: Widgets ID Widget 1 W1 2 W2 Widget_Making ID Person_Involved ID_Widgets 1 John 1 2 James 1 3 John 2 ...
0
votes
0answers
18 views

Creating database wherein there are multiple tables with same fields under different categories

We have vehicle/equipment repair workshops located at different places, these workshops send a monthly report in a standard format in MS Word file (attached a sample copy)to the headquarter(HQ). The ...
0
votes
2answers
47 views

How to interpret timestamps stored as 10-digit negative integers?

I've inherited an undocumented MS Access database where the date/time values are stored in an INT column and the majority of values I've seen so far are 10-digit negative integers. How do you ...
0
votes
0answers
43 views

Condition table theory

Is there a good way to create a "table of conditions"? I have a huge table, where 4-5 columns are considered in a set of rules, and based on those columns default values must appear in other columns. ...
1
vote
0answers
42 views

ODBC connects but authentication fails on users table

We have sql 2008 running in mixed mode. The site where sql is running the ODBC connection works and ms access authenticates against the user table. We have an IPSec vpn to a satellite office using ...
0
votes
0answers
146 views

SSMA cannot read meta data (missing runtime components)

What I want to do should be rather easy: Use SSMA to transfer internal Access tables and queries to SQL-Server. On my Notebook I'm running SQL-Server 2017 and Office 365 (32 bit) As I have 32 bit ...
0
votes
1answer
29 views

Help me to RANK correctly in Access

I have such a table: I want to rank the people, as who has the more V1 and the less V2, V3, V4, V5 be on top. Please advise me SQL code for MS-ACCESS.
0
votes
0answers
16 views

Comparing Across Tables to Find Most Recently Modified Row

all. Ive been working with some Sales Force data to solve some data synchronization problems we have between two instances of SFDC. Quick Background We have two instances of SFDC (SalesForce) that ...
1
vote
0answers
21 views

Selecting Duplicates on All Fields

I have an MS Access (no laughing at the back) database I've used to import a bunch of IIS logs into. Having looked at the Excel files I pulled these in from, I'm worried going by the dates that some ...
0
votes
0answers
42 views

Corrupted Access Database

I have a big Access database, with aobout 150 Tables/Queries/Linked txt files/Macros/Modules. I was working today, and then from out of nowhere the database got corrupted. I googled, and forund that ...
0
votes
1answer
26 views

msaccess split db feature linked tables

We have an Access db that has around 300 linked tables (to SQL Server tables). To switch environments, we run some VBA code that re-links all these tables to the relevant server. But this seems to be ...
0
votes
2answers
70 views

Get value from one field, and paste it in to a new column

I'm relatively new to Microsoft Access, and just a beginner with SQL programming. I have a table in MS Access. It is an import from a .txt file. In the first column There is a String with a Number or ...
0
votes
1answer
42 views

Calculation of two dependent variables in Access Query

I'm having problem constructing below two variables in Access Query or Access SQL which are dependent on each other, please kindly help! a(n)=b(n)+c(n); b(n)=if(a(n-1)<>0,a(n-1),d(n)). n refers ...
0
votes
0answers
27 views

Access data form entry

I am relatively new to Access and have a question. I am creating a database for an assisted living community, regarding Dietary Restrictions. My question is: I would like to create a front end user ...
0
votes
0answers
34 views

Revenue Forecast Database Design

I am currently trying to design an Access database that will house financial data containing forecasting, actuals and prior forecast data. The current format of the Excel "database" is Customer ...
0
votes
0answers
24 views

How do I create a query to show values in their relevent years

I have a table with records that have start and end dates per record. I need to create a query that based on the start and end date puts the relevent value into a financial year. For example | Record ...
0
votes
1answer
113 views

Unable to use today's date as a criteria in a query

Ok, been a good decade since I've worked with Access and SQL, so a little forgiveness please. Short version - when I add the query criteria to only display rows which match today's date, the query ...
1
vote
0answers
48 views

After reboot SSIS cannot connect to MS Access dbs

We rebooted our sql server 2016 machine. SSIS jobs that have been running fine are now unable to connect to access dbs. it throws the error: "XXXXX:Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE ...

1 2 3 4 5 13