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
1answer
1k views

How to import excel to MS Access table design?

NOTE: This is NOT asking how to export MS Access table design to excel. This is the opposite direction. Given a excel sheet, it has field info (name, start position, and width). I want to export this ...
1
vote
1answer
14 views

Is there a way to input field information by cvs file?

I am importing a new database. I am asked to input field information in MS Access manually. Is there a simpler way? For example, import a cvs version field information into the following
1
vote
1answer
38 views

MS Access fail to recognize correct field width

I am trying to import a very large text file (200 fields +) into MS Access. The text file is not comma separated. I am not sure if it can be called fixed length, since each field has it own length. ...
-1
votes
2answers
866 views

Select parent value if all child values are in a query

I have three tables similar to the example below T1 T3 ------------------------ -------------------------- T1ID X ... T3ID ...
0
votes
1answer
68 views

Archiving tables with Access frontend and linked SQL Server

I'm working on a project where the user has an Access 2013 MDB database with forms, reports, and queries. We're converting it to use linked tables to SQL Server 2008 R2. All the linked tables seem to ...
0
votes
0answers
24 views

Are there negative effects to creating multiple copies of an access database?

I've been working on updating an existing database for a company and for version control have been making multiple copies (so far I'm at 10) and making different kinds of changes to each one. I am ...
1
vote
1answer
246 views

Does modifying an access file copy affect original file?

This might me a stupid question, but it is crucial for me to know, so please bear with my limited knowledge of Access 2002 application. Let's say I have an original database file, let's call it ...
1
vote
1answer
109 views

Join two tables and create a new one in SQL

I am new to SQL and trying to apply a concept of Joins in MS-access. I have two tables Table 1 and Table 2: I want a query which will create a Table 3: Ideally i want all the data from both tables ...
1
vote
0answers
765 views

Using Microsoft Access front-end with PostgreSQL

We are using a MS Access database at work and I am exploring the idea of using the MS Access front-end and use a Postgres back-end to better manage concurrent uses and add spatial capabilities with ...
-1
votes
1answer
165 views

Access 2016 Web App View allow query null

I'm designing an Access 2016 Web App for inventoring our computers. Here you see the view: Some of those are queries. The problem is with SSD-Storage and HDD-Storage. The query looks like that: ...
0
votes
1answer
28 views

Concatenating time values

I have a problem. In my access database I have two fields with DateTime. I use them for when a meeting starts and ends. I would like to concatenate them for a report, but 09.00 ends up as 9.0 so my ...
3
votes
1answer
57 views

How do I duplicate a row in a source query to (X) identical rows in a destination table when (X) is qty in the source query row

Looking for help in below table using VBA in Ms-Access 2007. I have 39 columns to copy and repeat col40 times Just starting to learn VBA. Any help would be appreciated. I am attempting to take the ...
3
votes
3answers
3k views

JOIN ON syntax error in Microsoft Access 2016

I am querying a couple tables, and for some reason Microsoft Access 2016 continually informed me that my SQL query JOIN ON syntax is incorrect. Specifically, the JOIN syntax is highlighted, followed ...
0
votes
1answer
646 views

Simple access query based on many to many relationship

I have a simple many to many relationship set up on access with "List", "DayJunction" (Junction Table) and "Days". I am trying to query for records in "List" that have no related records in "Days". I ...
1
vote
2answers
482 views

Exclude table's data from another table

Is it possible to create a query and ask the following? I have two tables (each one has one column with data) and I want to obtain as a result all data from the first table MINUS the data from the ...
0
votes
2answers
5k views

How to restore SQL Server bak files in Access?

I backuped some SQL Server 2008 R2 databases using .bak files within SSMS, I sent them to a client and they're willing to open them within MS Access. Can you please tell me if this is possible and if ...
1
vote
0answers
80 views

Table Relationships: Table 1 (Field 7, Field 8, Field 9) to Table 2 (Field 1)

I'm sure that this is probably 'Databases 101', but I can't seem to figure it out. So, I'm setting up a database in Access - that I will want to move to MySQL, and I currently have 4 tables, set up ...
-2
votes
1answer
2k views

MS Access SQL multiple rows into one row with new columns

I have a table which contains individual test items (responses) from a 20 item test. There is a Person_ID, a Item_Number, and a Item_Response. Example: Person_ID| Item_Number| Item_Response 1 |...
0
votes
2answers
36 views

Make custom query from two table access

I have two table that table 1 contains Number, code, ID and table 2 contains Name, Family name, Number, ID, code, Phone number,... Now I want to make a query that contains table 2 fields but the ...
1
vote
1answer
321 views

How to simplify this query?

I am somewhat new to Access and I need help with a query that Access does not execute because of Not enough space on temporary disk. I have one table. A product may have up to 3 IDs and a place to ...
0
votes
3answers
2k views

Querying a many to many relationship in access

I have just created a many to many relationship in MS Access with three tables as shown below AE Career Fair - Struc contains a list of companies where the company name is distinct. Office Locations ...
0
votes
1answer
18 views

Storing information about office locations for international companies

I have a table in MS Access containing a list of about 500 companies and some information about them. I am trying to create another table that contains information about the office locations of these ...
0
votes
2answers
1k views

Is it possible to recover deleted tables?

One user deleted some tables from the database. Fortunately after that the base was not compressed and did not recover. Now I have a question. Is it possible to restore these tables, with all the ...
0
votes
1answer
16 views

On Load sub which checks records causing problems

I have a checkbox called 'Added to Schedule' on a form (booking sheet) which is supposed to be ticked if the job appears on another table (schedule). It usually works but sometimes if the job is then ...
0
votes
1answer
5k views

EXEC xp_dirtree

I developed a stored procedure with a call to this extended SP EXEC xp_dirtree @path, 1, 1 Database: SQL Server 2008 R2 Express I run the stored procedure from different sources: ASP. Net with ...
2
votes
0answers
537 views

#deleted in all fields using ODBC driver between PostgreSQL and Access 2013

I planning to use MS Access (2013) as a front end to a PostgreSQL database server (version 9.5.2). However, when updating a table in Access the new row appears with “#deleted” in every column until I ...
0
votes
3answers
2k views

Access: How to Combine Data from Two Rows in a Query

I have a base dataset formatted like the one below. Sex1 | Sex2 | Number| F | | 5 | M | | 8 | M | F | 5 | F | F | 3 | ...
1
vote
0answers
64 views

Create multiple rankings and a table

Here is my problem: I am using SQL for Access 2013; I have a table tbl with some fields Category, Subcategory and Value: Category | Subcategory | Value | | C1 | ...
-1
votes
1answer
26 views

How to take a field value and use it as a parameter in a URL

So I am trying to implement an automatic search of ebay completed listings of an item, based on the value of a field in my table. I am using access and I cant work out how to do this. I have tried ...
5
votes
2answers
4k views

ODBC Connections locking tables in SQL Server

We have an ERP system that is a little less than modern, the backend is currently sitting on a SQL Server 2008 R2 installation. We have through mandate of management (isn't that always the case) the ...
3
votes
2answers
132 views

Am I doing a security breach if I am connecting a MS-Access front end (multiple computers) to a SQL Server back-end?

I have a MS Access frontend that I would like to install on 5-7 computers, so they can access a SQL Server stored on a shared network drive: If possible, is it safe and can my data be corrupted? ...
0
votes
1answer
134 views

Where clause returns null, but need to return 0

Need an SQL Statement for the following: Table a partID partNumber Table b partID purchased SELECT a.partNumber FROM a WHERE (SELECT SUM(b.purchased) WHERE b.partID=a.partID) > 0 If ...
1
vote
1answer
745 views

Sharing Front-End Access Database

I have an Access back-end database with tables, queries, reports...and I have an Access front-end database with a data-entry form that will be used by 3 users. Both back- and front-ends are in same ...
3
votes
3answers
9k views

Access 2010-2016 Compatibility Error

I was just updated to Access 2016, while my users are still at Access 2010. I opened our database in MS Access 2016 once, didn't make any changes, and closed it out, and now all the users are having ...
1
vote
0answers
33 views

How do I hide the “read …save as” notification for an accdr file

When I make the accdb file to an accdr file and open it with Access 2010, the database is created with Access 2010 and OpenGateSoftware’s UI builder. I get a notification bar at the top saying: Read ...
0
votes
1answer
4k views

Query showing a number instead of the column value

I have a query (query_clients) based on a table (Person). One of the columns I am pulling is a StateAbbr column, which is a lookup to the StateAbbr table. The primary key in the StateAbbr table is the ...
1
vote
3answers
2k views

MS Access Unable to Select Number Field Size

I'm using Microsoft Access 2016 32bit on Windows 10. My issue is the Field Size property list is empty for the Number data type. Why is this empty? How can I change a number's field size? I am trying ...
4
votes
5answers
1k views

How can I allow part of a key to be null and enforce uniqueness of records with nulls?

I have an Access table called Engagement with four fields: Emp_id, Year, Week, Act_id It records when an employee is/was engaged in an activity. Each field is Number and each makes up part of the ...
2
votes
2answers
942 views

Non unique one to many relationship

I am a noob to database design so bear with me. I have two tables in Microsoft Access: Table 1: | ID | Field1 | Field2 | |:---|--------|:-------| | 1 | A | 1 | | 2 | A | 2 | | ...
1
vote
1answer
50 views

Search a Table for a Lack of Records

Please understand this is not my field. I have inherited an access database from a previous employee. I have a database which logs training so I have 1 table with employees names trade etc. I then ...
1
vote
1answer
98 views

SQL query to check a table against a “rules table”

I have a table PIT that looks like this: PIT_TYPE PIT_STYPE PIT_APID 1 1 1313 1 7 1313 3 25 1316 247 1 1318 247 ...
1
vote
2answers
1k views

How to connect to a password protected Access 2010 database from SQL Server 2014 x64

I have the following situation: An Access 2010 database (.accdb) on a shared folder of a remote server. A domain account that can access the shared folder and files. A SQL Server 2014 x64 running on ...
-1
votes
2answers
38 views

Tryng to use and or queries

In MS Access, I have a table with six columns. A name and (let's say pets) so the columns are marked cat, dog, fish, rabbit and hamster. I want to do a query that I can check cat and it will show all ...
2
votes
0answers
127 views

Distributing MS Access Database

I develop an application that saves records in MS Access DB. Each user has it own DB copy on his local machine, and there is a central DB that reflects all the copies from everyone. The central DB is ...
0
votes
2answers
250 views

Access Web App query 3 Tables with aggregatios - Need a workaround

I'm designing an Access 2016 Web App for inventoring our computers. The problem I have is with a query where I want to get the CPUCore-amount and RAM-Amount for each PC. The computer-value in RAM ...
1
vote
1answer
249 views

Is DISTINCTROW in MS-Access the same as selecting all the fields and doing a group by on all of them?

I was looking though some queries in MS Access, and I found an SQL keyword I was unfamilar with called DISTINCTROW, from what I can tell, it's the same as an SQL GROUP BY which includes all the fields,...
0
votes
1answer
44 views

How to create a table in access and automatically fills it with information from a query? [closed]

Is it possible if I create a table that will lookup for a value filtered from a query (consult), in this case is duplicated "Name"s from a Employees registring table and automaticly fills up that ...
1
vote
2answers
94 views

Help with Access many-to-many relationship (beginner)

I'm a beginner with MS Access hoping for some help with a database I am trying to create. This is (will be) a database of land parcels in a given area, and their associated landowners. Here are my ...
0
votes
1answer
3k views

MS Access Data Type for time in hh:mm:ss format

My database contains three different sporting events with finishing times imported from an Excel spreadsheet in the hh:mm:ss format. How do I customize the Access data type field to accept this ...
0
votes
1answer
346 views

Can't list columns or query in MS Access 2003 linked server in SQL Server 2008 R2 using Microsoft Jet 4.0 OLE DB Provider?

I can however list the tables... If I run Exec sp_tables_ex 'LSTest' I get a result with a list of tables. But if I run Exec sp_columns_ex 'LSTest', '<one of those table names>' I get an ...