Show results of no matches between two tables based on a date


I have two tables, drivers and trips. I want to show which drivers are not used on a specific day on trips (based on a text box field on a form). I have a trip date field on trips to use for the comparison.

I don’t think i can do this with an unmatched query, and having trouble finding SQL i can use for this.

Any help is appreciated.

submitted by /u/mrkoss
[link] [comments]

Posted on

Copying(?) record data to search another form

The Problem

My switchboard has multiple buttons to open forms. Every time I switch forms, I have to search for the record I was JUST working on. Example: on Form1, I’ve navigated to Record446. When I go to Form2, the default Record1 shows. and I have to go find Record446, manually….again.

The Desired Solution

Wouldn’t it be great to have Form2 load Record446 (or Record50, Record 23, etc.). Especially because I’m switching forms so much.

What I’ve Found Out

I found the “On Deactivate” Event for the forms (Form1), and wouldn’t I put a macro in there to copy the information? I’m not sure what or where or how to copy the field data.

Then, I would have data when “On Activate” event occurs for Form2, where I run “Find Record” the same as the copied record.

Or maybe there’s an easier, automatic way to do this?

submitted by /u/megadecimal
[link] [comments]

Posted on

Split Form to Filter Determine Form Input Options?

Hi there! I am trying to set up a production tracking system for a variety of products whose ingredients and quantities are stored in a BoM table. I have a Query that pulls together all the relevant fields from Products table, Ingredients table, etc and I have a split form that filters the relevant ingredients based on product selection. Now I am trying to set up user input so that they record the product type, the quantity of each of the products’ ingredients, and the lot# of the ingredient: these will all be stored under a productionID in a production table.

My issue is setting up user input that is dependent on the split form entries (one for each ingredient in the selected product). Any help or advice would be very appreciated. Much Love.

submitted by /u/llolo96
[link] [comments]

Posted on

Secure authentication to Azure SQL Database

Hi, all.

The company I work for has an old Access database that I’d like to move the back end to Azure SQL Server.

I think the best way to do this is to migrate all the data to the new Azure db, and then link the tables in the back end file to their azure counterparts (although, if anyone has any suggestions otherwise, they’re welcome!). I have already migrated all of the data to the new azure db.

Setup details:

  • The Access db has been split into a back end and ~10 different front ends (for different departments/roles)
  • The front-ends will be stored on a mapped network drive (to a network share)
  • Approximately 25 people will be using the db from their Active Directory joined computers
  • We do have an Azure AD, it is NOT federated (so, no AD FS), but we do have Seamless SingleSSO enabled

The problem lies in the fact that I don’t want users to have to enter a password every time, and I’d rather not store credentials/passwords in not-secure ways. Interestingly enough, on one of the documentation pages from MS about linking Access to on-prem/Azure SQL Server, they specifically note:

If you use SQL Server Authentication, your password is stored in clear text on the linked Access tables. We recommend using Windows Authentication.

… despite the fact that Windows Authentication cannot be used with Azure SQL Databases! (At least, not that I know of). To some extent the easiest and most secure thing would be to use the “Active Directory Password” authentication method, and just have users enter their passwords every time. However, the application requires short but frequent use. So, unless we just started having users leave Access open the whole time, they’d need to enter their passwords very frequently. (Or maybe there’s another way I don’t know of to leave a connection open for a while?)

I’ve looked a bit into using an access token, and I think that’s the most promising avenue for the moment, but I don’t know how well it will work. I’m wondering if SSO enabled would allow the connection WITHOUT any additional authentication, but I don’t know if the ODBC driver (or any provider for that matter) would be able to make use of that.

I’ve also had some out-of-the-box ideas like just using SQL authentication with the password stored in a file on the network drive, but automating a task to run every night to change the password and update the file.

Have other people solved similar problems?

Am I being paranoid?

Any suggestions/advice/direction pointing is welcomed.


P.S. I apologize if it would have been to post to /r/AZURE. I think I will post something there next, but I thought I’d post here first!

submitted by /u/dedroia
[link] [comments]

Posted on

First time using access have few question

just a background. im thinking of using access to build a gui for an inventory system i had as an idea. i have background on sql and plsql and also a little bit of programming.

1.) i have 2 db installed in my system mysql and postgresql which of the 2 would be most compatible and recommended for access
2) do you need to code in order to link to another table while entering data? i think itll be easier to understand with an example below
sales form
sales order number:
first name:
last name:
ordered date:
for this i have 2 table sales and customer. a. is it possible to pick customer data on the sales order form? do i need to code or just drag and drop and change an option?
b. i want the customer first and last name to be linked to the customer table so if i change anything in the customer table it will reflect on the sales order form.
c. is it possible to also auto fill the contact if i selected a customer?
3.) is it possible to make access not obvious that it was made by access or do i really need to open access?

submitted by /u/TonyTanduay
[link] [comments]

Posted on

Is there a way to copy and paste a column of data into the design view of a query?

Forgive my ignorance, but I have limited knowledge of Access. I do some data compiling for my work every quarter using Access and Excel and I have step by step instructions. I have to enter in a lot of codes into the design view of access and it is very time consuming. Is there anyway I can have it all typed up one time and then copy and paste it into access? I attached a photo below to hopefully better explain what I am asking.

submitted by /u/perroloco11
[link] [comments]

Posted on

How to create a Number with 000000000000 input mask

Hi, so i’m a student and have an assignment with access using access. One of the instructions says to “Create a Number with 000000000000 input mask” Although I know how to create input masks, in the input mask wizard there’s no format that fits this amount of 0s. All I see is phone number, social security number, zip code, etc. If anyone could help with this I’d greatly appreciate it, as my TAs and professor seem to have not covered it and haven’t gotten back to me. Thanks.

submitted by /u/MaxwellpHu
[link] [comments]

Posted on

Form to browse long text fields with short text index.

I have a table like this: NoteDateTime DateTime Description ShortText Narrative Long Text

I’m trying to create a form so that on the left side of the screen there is a list containing the short text and description of all the rows. When I click on a row, the right side of the screen populates the narrative.

How can I go about this? Thanks!

submitted by /u/DevilDoc44
[link] [comments]

Posted on

Have a table with dated updates to events with unique IDs (per event). How do I write a query to give me both the first and last occurrence of each event?

Each row in a table relates to a dated update for an event with a unique ID.

How do I write a query to look up each event ID and then give me the first and last dates ?

I’ve done this in Excel but don’t know how to do it in Access.

Hoping this illustrates…

=IF(A150=””,””,IFERROR(AGGREGATE(15,6,’Sheet1′!B:B/(‘Sheet1′!A:A=A150),1),””)) gives me the first occurrence, and =IF(A150=””,””,IFERROR(AGGREGATE(14,6,’Sheet1’!B:B/(‘Sheet1’!A:A=A150),1),””)) gives me the last occurrence.

A in Sheet1 is the Unique ID, but duplicated for each update to the event. B is the date/time of the update.

Ultimately all I’m interested in is the number of days between these dates, but unsure how else I could get it without getting these two dates first.

Any chance anyone knows how to help?

submitted by /u/toni_inot
[link] [comments]

Posted on