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
166 views

MS Access - Creating a report based on a pass-through query that requires a parameter

I am attempting to make a report in Access that uses parameters as well as a pass-through query. I'm open to using a temporary table but the guides I can find using temporary tables all use an ...
1
vote
0answers
26 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 ...
4
votes
1answer
71k views

IF IS NULL then SINGLE else keep the value of the field

I am working with a DB in Access and I'd like to be able to fill the field in this row with a result of SINGLE if it is not already filled with another value. I wrote Iif(MaritalStatus) is null, "...
0
votes
0answers
10 views

Import Excel sheet to access database using vb.net [closed]

I am developing software in vb.net for sales invoices. Where I need to import invoice data from excel file to vb.net Grid Data View and then save into access DB. I will use DB data in the future for ...
0
votes
0answers
34 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
0answers
10 views

access fill text box in one form in black when i focus the text box [migrated]

realy new to working in access. i am trying to build an inventory in access to a friend. i am building a login form which contains two text box. when i focus one of them, the text box is filled ...
0
votes
1answer
448 views

Linking MS Access 2010 to Outlook Inbox: Including Attachments

I'm trying to set up a MS Access table linked to an Outlook folder. I've been able to use the external data wizard to set up a linked table, but the table seems to only include subject lines and ...
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
0answers
9 views

Fill item Id in form based on a combo box that selects item name. MS-Access [migrated]

I have a Sales form that contains the ItemSoldID field. It was difficult to write itemSoldID for every item by memory because new items are frequently added and the list has reached to More than 100 ...
1
vote
1answer
115 views

MS Access : Navigation pane is suppressed

I received a MS Access form client. It is in access 2007 format. Initially Navigation Pane was not available. Only form was visible. Then I choose options->Current database->checked Display Navigation ...
0
votes
1answer
26 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.
2
votes
2answers
915 views

Splitting 1 record on one table to become two records on another table

I am REALLY very new to this so please excuse my ignorance, but I could really use some help! I've got two tables: BillsFiled and Authors On BillsFiled, there are the columns: ID, Symbol, Filedate, ...
1
vote
0answers
711 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 ...
0
votes
1answer
483 views

Sql statement to count sum and campare it with another table

I would like to write an sql statement in microsoft access that group items and count their sum in order to compare it to another table I have two table like below : Product: list of demanded ...
0
votes
1answer
128 views

Setting up relationships properly with the Access UI

I am making the database design of my website in Microsoft Access 2013 because it has a nice user interface and I believe it will help me understand everything a whole lot better with the UI. I have ...
0
votes
1answer
107 views

How do I count and order by unique entries?

I have this: SELECT tblClient.[Printed Name], tblClient.[Address 1] , tblClient.[Address 2], tblClient.City, tblClient.State , tblClient.Zip, tblClient.Phone, tblClient.[Email Address] FROM ...
3
votes
1answer
689 views

Update Table Query: Automatically handle “Enter Parameter Value” prompts?

EDIT: Here are two database Files that demonstrate an applied example of the problem: Example_DBs.zip (Zipped due to 2 link limitation.) Note: The missing Field in "Not Working.mdb" is intentional....
2
votes
1answer
101 views

Form isn't reading table records

I am really new at dB building. I created a table that is to read from records of a few other tables. However, i needed to add another table and i placed the input field into the form. My problem is ...
1
vote
1answer
105 views

How do I calculate a field based on multiple non-numeric records

I have a several tables that I have joined together to get raw data. Some information about my query in the most basic form: Table1 has a column [FacilityID] that is a unique identifier in that ...
1
vote
1answer
86 views

Getting intervals between time from all succeeding rows? (Time is in text data type)

NAME DATE TIME INTERVAL a 10/19/2015 5:00 PM a 10/20/2015 5:00 PM 86400 a 10/20/2015 5:30 PM 1800 This is easy to do in Excel. But considering we ...
1
vote
1answer
105 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 ...
0
votes
0answers
14 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
3answers
197 views

Restrict Front Ended User access to Form entry only

I have a front end database that I want to restrict user access to only enter data into a form. I do not want them to be able to scroll through other entries via the form. How can I restrict this in ...
1
vote
1answer
84 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 ...
0
votes
1answer
89 views

Handling empty fields

SELECT Name, SUM(Switch([Date] = DateAdd('d', - 7, DATE ()), NetAmount) )AS DAY1 FROM Customer GROUP BY Name My question is how to return the value 0 when the date is not present (an empty field). ...
1
vote
1answer
103 views

Access Data stored in SQL Server

I'm currently working on a project to convert our Access Apps to store the table data in SQL Server. Using SSMA to migrate the data results in the foreign keys and Look-ups work as expected in ...
1
vote
1answer
89 views

How to Prevent Timeslot Overlap

SQL newbie here. I currently have a 2-table database-"Customers" and "Bookings". This question concerns the "Booking" table, which has 3 attributes this question is about: BookDate BookStart BookEnd ...
0
votes
0answers
39 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
21 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
65 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
195 views

Combining GROUP BY and JOIN

This seems like a really simple request, but I can't find the answer. I'll admit that I only have a very limited understanding of JOINs, so it could just be that I just don't fully understand the ...
0
votes
1answer
40 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 ...
2
votes
1answer
153 views

Relationship between two multi-valued child tables

Scenario In my system, there are a number of different TradeBaskets. Each TradeBasket contains a number of different Securities. The Securities have a Type, Value, Sector etc... and new Securities ...
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
1answer
160 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
138 views

“key violation” when trying to import data into MS Access

I am trying to import data into an MS Access table from a flat ASCII file. I've done this many times before but now I am getting a "key violation." There is only one key in the table and it's an ...
0
votes
0answers
24 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
23 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
81 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
35 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 ...
0
votes
1answer
61 views

Trigger Access table on Insert

I am using Access 2016 and building an in-house HR app for my team. I have a Position Table, an Employee Table, a Position_Employee Table and a Staffing Table Position Table ------------- ...
0
votes
0answers
26 views

Tables not showing up when linking SQL Server to Access using ODBC connection

I am learning to link the back end of a database in SQL Server express 2016 to the front end in Access 2016. The tables were originally in Access and then I exported them to SQL Server. Now I am ...
0
votes
1answer
333 views

Access Macro: ApplyFilter w/values from 2 combo boxes

I'm getting started with databases in Access for a work project (technically a work-learn for my non-CompSci degree). I have experience using multiple filters on multiple columns through Access/Excel, ...
0
votes
1answer
282 views

Concatenate fields in access

How come my statement is getting marked wrong? The question is: Create a query based on the tblCustomer and tblPurchase tables that will concatenate the member’s first and last names (separated by a ...
0
votes
1answer
1k views

Inputting data from one table into another table in Access

I have one table that shows all transactions with fields, chequeNum and accTransaction. It'll look something like this but a thousand more entries. **chequeNum accTransaction** ...
0
votes
1answer
364 views

How to Input a time series set of data into MS Access 2010

I am trying to create a database with time series data in MS Access 2010. More specifically, I have order history over 30 months. What is the most efficient way to input this into MS Access? ...
0
votes
2answers
46 views

Example of MS Access SQL IIF to T-SQL

Given the syntax rules shown below Access: IF(Condition, ReturnIfTrue, ReturnIfValue) T-SQL: CASE WHEN Condition THEN ReturnIfTrue ELSE ReturnIfFalse END What would the below be formatted to T-SQL ...
0
votes
2answers
239 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
votes
1answer
170 views

Select ONLY Max(date) when there's repeating dates

Here is a sample of the data contained in my Tracking table: tracking_id date_action action_id staffing_id ------------- ----------- ----------- ----------- 1 2019/03/04 4 ...