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

learn more… | top users | synonyms

0
votes
2answers
1k views

Sum of different values on the same row in a table

I have a table with numbers from 0-5, under different field names (Score1, Score2 etc. all the way to Score50) that need to be summed at the last column of that same row. Each row represents a ...
0
votes
2answers
324 views

linked server to specific access table

I have built a linked server which links my SQL Server database to an Access DB. Using this object someone is able to see all tables in Access. What I would like to do is to isolate it just to 1 ...
1
vote
2answers
276 views

Advanced CSV export formatting in MS Access

I am trying to create a CSV file with the following format (for import into MYOB AccountRight financial software): InvoiceNum1,Line1,... InvoiceNum1,Line2,... InvoiceNum2,Line1,... ...
0
votes
2answers
512 views

Connect to Microsoft Access database via Microsoft SQL Server / MySQL

A client has an Microsoft Access database where they save their products. To access the database now you have to login via VPN and open Microsoft Office Access. I need to access this database from a ...
1
vote
0answers
243 views

SET IDENTITY_INSERT equivalent in Access

In sql server i can explicitly insert value to a identity column of a table by using SET IDENTITY_INSERT ON . For an Access database i am using Autonumber column datatype. For this column i am not ...
0
votes
1answer
715 views

Grouping and counting rows by existence of matching criteria in any of several columns

Disclaimer: I've inherited a database which is probably not quite as "normal" as I would like, and I'm limited to Jet/MS Access 2010 for my db backend and interface, as well my choice of reporting ...
1
vote
0answers
47 views

A pre-existing hierarchial table as relational

Currently the database contains something like so (these are the main fields but there's a large amount more): +----------+----------------+----------+ | Agency | parent | main_term ...
0
votes
1answer
566 views

MS Access: error 3045 while linking to back-end

In our environment multiple users each work with their own copied front-end, with tables linked to a single networked back-end (both files are .mdb). Each user has permissions to read and write to ...
1
vote
1answer
94 views

representation in ms-access

I have a database in microsoft access. I want to know how to look up a singular datum from a reference table giving a dynamic set of values. Here is a representation of what I mean: I have the ...
1
vote
1answer
2k views

Returning a True or False from an IIF statement

I have a query with the following IIF statement OnlineEnrolment: IIf([qry_ebs_final_CAN-ENROL-ONLINE].[SP_CAN_ENROL]='Y',True,False) It gives the correct results, but the format that it gives them ...
0
votes
1answer
134 views

Create primary keys based upon higher-level entity in access?

Is it possible to have access create primary keys for items by appending a string to the primary key one level above it? I am trying to create an inventory management database for my company's server ...
1
vote
0answers
124 views

Supressing the Confirm box in an access vba function

I've got an Access database and in there I have macro which outputs a query to a csv file ready for import into another system we are using. As part of this macro it makes a backup of the base table ...
0
votes
1answer
102 views

Can I update an Access table with an Oracle table?

I have an Access database with a table with a unique key and price field among other fields. I have been told I have access to an Oracle database with those prices in it and the linking Key. Can I ...
-1
votes
2answers
164 views

what is the best way to update 2 tables via data entry form in access 2007 database? [closed]

I have a database with more than 500.000 records, I am using Table_a to Accessing the files by the ID number,Name, Date, Address, Tel, ..... And then I used Table_b to archive files and locate the ...
1
vote
1answer
179 views

Access 2007 Work in a separate file using ACCDE

Have been working in an Access 2007 application for over 3 weeks and it should be ready now. Anyway, I know (and tried) that "Make ACCDE" can actually split the raw data from the application itself so ...
2
votes
1answer
9k views

Merging two Access tables into one

I have mostly identical tables, however there are slightly differences like missing columns in older ones. I want to merge 5 tables into one. Is there any easy way to do this?
2
votes
2answers
5k views

MySQL Workbench connect to MS Access

I have a .mdb file on my computer that I would like to convert into a MySQL Schema and I have found that there is a migration tool within Workbench. However it doesn't seem to allow you to connect to ...
0
votes
0answers
234 views

Missing values in INNER JOIN MS-Access?

When I run the following query in MS-Access: SELECT a1.ConStateNumber, a2.ConStateNumber FROM qryW2_yr_sum_slash_qryW2_q4_sum_result a1 INNER JOIN qryW2_q3_sum a2 ON a1.ConStateNumber = ...
0
votes
1answer
53 views

Get Maximum Value

I am using MS Access database. I have a table (Proposal) with the following properties: ProposalID - PK, Title, RequestedAmount, ResearcherID - FK I want to run an SQL query to get the candidate that ...
8
votes
1answer
500 views

Normalizing a database without access to source data?

I've started a new role which deals with large amount of related data. Our source of all this data is various Excel dumps pulled from databases we do not have access to. The previous person filling ...
0
votes
2answers
93 views

How to write a script to push the server time to out?

Background: I am programming a database in Ms-Access and using SQL-server as the Back-end. But since my database will be a multi-user platform I need to make sure that whenever a user is posting data ...
2
votes
1answer
319 views

Would using timestamps solve consistency issues with snapshot isolation?

I'm considering using Snapshot Isolation for some large queries being send from an Access 2007 frontend. These queries are occasionally causing ASYNC_NETWORK_IO waits, and some blocking as well. I'm ...
7
votes
7answers
1k views

Main Considerations When Moving From MS Access Programming to SQL Server

I am a 100% self taught MS Access programmer (main part of my job is programming), I am now building larger databases; still using MS Access as the UI but SQL Server to store all data and do more of ...
1
vote
2answers
9k views

Backup Access Database Daily/Weekly

We have a Microsoft Access database split into backend/frontend. What I am looking for is to be able to automatically backup these files on a daily or weekly basis - what is the best way to go about ...
2
votes
3answers
1k views

Database Design Relationship for Patient Visit in MS Access

I am designing a database to store records for research purposes, and I am having trouble picturing how I can create a relational database based on the information I have. There are a total of 6 ...
2
votes
2answers
3k views

Ms-Access 2007-2010 SQL - from single row record how to create multiple rows

I need help in below table updation using SQL in Ms-Access 2007-2010 database. I've a table called table1 which has below entries: table1: -------------------------------------------------- | col1 ...
1
vote
1answer
304 views

Database Relational Design for Multiple Categories

I am building a database for participants who are grouped into studies. There's a total of 5 different studies - A, B, C, D, and E. The participants may be in more than one studies, and participants ...
3
votes
1answer
106 views

How can we achive the replica of databases for SQL Server?

I have been working in VB6 to VB.NET migration project.In VB6 project they have individual databases (access db .mdb) for every user now we made that to centralized database using SQL Server. In the ...
1
vote
0answers
33 views

Defining the Relationship - Some Basic Help [closed]

I thought I had this figured out but I am starting to doubt myself on something that should be simple. I have a table of Group, and a table for Activity. Each Group has one Activity. Different groups ...
0
votes
2answers
3k views

MS Access ER diagram

I have database scheme for Microsoft Access. I'm interested how I can convert the scheme into an ER diagram? Is there any tool that can do this based on scheme relations?
0
votes
2answers
281 views

Access 2003 (SQL Server 2000) migration to SQL Azure

As my old Windows 2003 RAID controller started throwing errors, I am seriously thinking about switching current Access 2003 (adp/ADO) clients to use a Windows SQL Azure solution, in place of current ...
2
votes
2answers
221 views

Pull Access schema into a SQL Server table

We had an assembly that used to pull a list of tables and their columns from an Access database and place the listing into a SQL Server table for later use. For various reasons the assembly is no ...
0
votes
1answer
192 views

Microsoft Access - performance difference between index and constraint?

I've looked around a bit but haven't come across anything talking specifically about MS Access. So: what, if any, is the performance difference between a field with a constraint and a field with an ...
0
votes
0answers
2k views

MS Access trigger

I got a trigger to update a table in SQL. My actual need is in MS Access but I could not make it. So please help me to write the same trigger in MS Access. Query is as follows: Create trigger ...
0
votes
1answer
5k views

How to set up data entry in many-to-many database in Access 2010 with forms/subforms?

I have a simple two table database in Access 2010 tracking landowners and properties. They are related in a many-to-many (M:M) relationship with a link table. Forms for viewing data have been ...
1
vote
1answer
1k views

Missing 'Microsoft Office 12.0 Access Database Engine' in SSMS

I've got several users that need to import / export ACCDB Access files into out of SQL Server. Unfortunately, I have been unable to get 'Microsoft Office 12.0 Access Database Engine' to show up as a ...
1
vote
1answer
81 views

What is a proper way to handle a “sequence number” like field?

The fields of a trimmed-down version of a database I'm working with looks like this: Model Number, Model Name, Customer, Date, SeqNum The customer can purchase the exact same item on the same date ...
3
votes
1answer
2k views

Access 2010 linked table - solve the “decimal field's precision” error

I have an Access 2003 database (running on Win 7 64-bit, Office is also 64-bit) that has many linked tables pointing to an Oracle 11g instance through 64-bit Oracle ODBC driver. Most of the tables ...
1
vote
1answer
806 views

Connecting to a SQL Server database from a Flash program [closed]

I currently have the ability to utilize Microsoft SQL Server 2012. I am developing a project with Adobe Flash Builder 4.7. If I link my database with Adobe Flash Builder is there any additional ...
4
votes
1answer
856 views

Removing duplicates from a query with conditions in Access 2007

I have a table of transactions with 880,095 records that I am querying. There are duplicate Transaction ID's in the table that I would like hidden/removed when querying the table. Eg 1. ...
1
vote
1answer
215 views

Pivot a table to repeat the first column for each following column

i'm not sure how to explain so I'll try and write out an example. I'm currently using an access database with a table in the following format 1 A B C D 2 A B C D I'm trying to perform a query ...
3
votes
1answer
230 views

I need help to migrate denormalized table content to normalized form- MS Access

I have a staff database where we are storing information in Normalized form. There are 3 entity tables and 2 mapping tables connecting these with m-n relationships However, for ease of collecting ...
3
votes
2answers
345 views

COUNT (*) = 0 does not work?

Alright, I'm trying to make a query for a library that will show which students have never borrowed a book. For this, I have done the following: SELECT LEERLINGEN.LLNR, LEERLINGEN.VOORNAAM, ...
6
votes
1answer
4k views

MS Access database in a network

I would like to ask if MS Access database supports working in networks & multi-user access?
2
votes
0answers
573 views

Parameters in queries using vba

I am trying to write a process to run several prewritten Queries in a row without any human interaction. I tried to write a macro, but the after saving it it still prompted for the date range in the ...
2
votes
1answer
100 views

I get the error “You are attempting to execute a query in which the selected expression is no part of a statistical function.” in MS Access 2010?

Please note: my copy of Access is in Dutch, so any English parts are translated from Dutch. I'm currently working on a database in Access using SQL, with the exercise being: Create a query that will ...
3
votes
1answer
7k views

Joining 3 tables using SQL Query?

I'm having problems joining three tables. It works well when I use the same query for joining two tables (after removing LEFT OUTER JOIN C ON B.No = C.No). However, it doesn't work when I use three ...
0
votes
1answer
1k views

Access query to select multiple records with matching field

I have a table with a list of people ([FirstName], [Surname], [Age]). Some of these people are in the same family. I'd like to return a list of people who have another family member in the table (same ...
0
votes
1answer
599 views

May a query update a source table?

I work with a few tables with given data. I run a query built in Design View just like result:![table1].[qty]*![table2].[expenses] and I get the output in query table in field result. Is it possible ...
1
vote
0answers
2k views

how to connect values of a new record in a form to a table? [closed]

I have just started working on MS Access 2007. I know how to create a form and add buttons, textboxes, etc I don't know how to connect my values entered in textbox to my table in database. How do I ...