Microsoft Access

221K

Solutions

52K

Contributors

Microsoft Access is a rapid application development (RAD) relational database tool. Access can be used for both desktop and web-based applications, and uses VBA (Visual Basic for Applications) as its coding language.

Share tech news, updates, or what's on your mind.

Sign up to Post

Access 365 query question.

Table fields are:
   [Date of class]
   [Class Type]
   number of [Students Completed].

I need a query that shows,
   Year: DatePart("yyyy",[Date of Class])
   Count of [Class Type]
   [Class Type]
   Sum of [Students Completed]
0
original inputevery day I am getting the file which supposed to be uploaded in the database.
it has several dups, you can see from the above.
The common :
1. if the record is completed duplicate (by all fields) - the second one should be deleted
2. sometimes the only difference is the Status-- in this case, I need to delete whatever is not Success
final supposed to be
that is what i need to have
TestDB.accdb
0
Here's my dilemma.
I have two tables, tblPOAM and tblPOAMMilestones.  PK/FK linked
The tables, queries and forms work just fine.

I can export just the tblPOAM to an excel preformatted template.
But I can not get the tblPOAMMilestones to export correctly.

The Milestones need to show under the POAM item.

See attachment for an example of the output.  Notice that rows 2-4 have the same key and 4 and 5 do not.  

Maybe I am going about this the wrong way so any suggestions would be helpful.
0
Hi
I would like to be sending some short messages system (SMS) from Ms Access App to my colleagues for any urgent issue at hand from my laptop, for the URL I have to organize from a service provider for a fee. Now instead of using a modem, I want use REST API, I have to get URL from the service provider. Below is my scant VBA code which I intend to use, also help to complete !

SMS CODE

Private Sub CmdSmsLines_Click()
Dim strURL As String
  Dim neMb As String
  Dim msGe As String
  Dim appIE As Object
  
    Set appIE = CreateObject("InternetExplorer.Application")
    neMb = Me.txtneMb.Value
    msGe = Me.txtmsGe.Value
    
    
    strURL = "http://bhashsms.com/api/XXXXX=success&pass=RRRRR&sender=ZZZZZ=" & neMb & "text=" & msGe & "priority=ndnd&stype = normal"
       
         
   With appIE
        .Navigate strURL
        ' uncomment the line below if you want to watch the code execute, or for debugging
        '.Visible = True
       
    End With
    ' loop until the page finishes loading
    Do While appIE.busy
        DoEvents
    Loop

    appIE.Quit
End Sub

Open in new window

I intend to use two controls as in memo form see below:
cellNumber:
SMSMessages:

Suppose I want to send the message to 4 people at one go, how do I type in the phone numbers in the memo control box SMSMessages: or msGe
0
how can i integrate excel with access? For example, We have a database file (.accdb) on a sharepoint, that I would like to pull data from into Excel systematically (call it once a day). I then want to use that data and run various analytics off of it/generate reports.
0
I have a form that a user enters a zip code and the form can then lookup cities... some cities share the same zip code (technically the same city)  In the past I would look up the zip and if more than 1 using send keys I would display the drop down in the combo box showing just the options for that zip:

Me.cbZip.RowSource = "SELECT tZip.Zip, tZip.City, tZip.State FROM tZip WHERE (((tZip.Zip)=[Forms]!        Me.cbZip.RowSource = "SELECT tZip.Zip, tZip.City, tZip.State FROM tZip WHERE (((tZip.Zip)=[Forms]![FEntry]![cbzip])) ORDER BY tZip.City, tZip.Zip"

SendKeys "{TAB}", True 'move out of combo
SendKeys "+{TAB}", True 'move back into combo
SendKeys "(%{DOWN})", False 'display new findings


However I don't think Send keys is support  any more.... any way to make this work with the same behavior as with the send keys?
0
I would like to track printer usage and identify underutilized printers.  I collected information from the printers showing the number of B&W pages, the number of color pages and the date checked.
I would like to be able to update the page counts and then run a report showing the number of pages printed and the number of work days between the dates checked.

The goal is to identify printers that aren't being fully utilized.  I have been trying to use the DMax function to select the records with the latest date checked.  I am not sure how best to go about identifying the latest counts, vs the previous, etc..
I would like to be able to collect the usage data at regular intervals and then produce a standard report.

Any help that can be provided is appreciated.
Printer_Pagecounts.xlsx
Printers_Test.accdb
0
I need to remove duplicate words from within a string.  I found code to do it and it works but a little too well.  The code below will remove anything that is repeated again even if it is just part of a word.

Ex:  construction company t shirts construction shirt construction co t shirt
Becomes:  construction company shirts
What I want: construction company t shirts shirt co


How can I make this code remove the duplicates while not removing anything that is part of another word?

Public Function EliminateDupesInString(strText As String, Optional strDelim As String = " ") As String
    ' remove duplicate strings from within a longer string
    ' "AIRTEX 1241 AIRTEX AW3409 CHRYSLER 7700598025 CHRYSLER JR775056 DELPHI DD1265 DELPHI DD1666 DELPHI WP1039 DELPHI WP1747 DELPHI WP2235 RENAULT 7700598025"
    '  becomes "AIRTEX 1241 AW3409 CHRYSLER 7700598025 JR775056 DELPHI DD1265 DD1666 WP1039 WP1747 WP2235 RENAULT"
    Dim varArray As Variant
    Dim intI As Integer
    Dim strOut As String
    strText = " " & strText & " "
    varArray = Split(strText, strDelim)
    For intI = 0 To UBound(varArray) - 1
        If InStr(strOut, varArray(intI)) = 0 Then
            strOut = strOut & varArray(intI) & " "
        End If
        'Debug.Print varArray(intI)
    Next
    EliminateDupesInString = Trim(strOut)
End Function
0
Hi

I have an Access table called "Manufacturers". It has an AutoNumber column called "ManufacturerPK" and a text column called "Manufacturer Name".  I am constantly being given new lists in Excel that must be used to replace the entire table in the order that it appears in Excel. This creates a nightmare because
the key values in the "ManufacturerPK" column are already being used all over the database so I need to make sure that the same "ManufacturerPK" matches each "Manufacturer Name". How do I reload the table with new information without losing these matches?
Thanks
0
I am using MS Access 2003... we have a few (about 8 reports) that I had linked our logo within the report... the DB file size was about 50MB.  For various reasons I wanted to embed the 18KB graphic into each report.  After doing that the DB size increase over 2x to 110MB!  I tried:

- Compact and repair
- De compile and recompiling
- Creating a new DB and importing all my tables and forms

All had zero impact on the DB size.  I remove the embedded images and DB size was back down.

How can 8, 18KB image cause the DB to double in size!??!?!  Aside from linking is more efficient way to embed images aside from the graphical properties of the image as "embedded"?

Thank you.
0
Hi

I have a combobox with the following datasource. I have a text box that I want to be automatically populated with the item in the second column of what is selected in the ComboBox. I am using =[cboManuf].[column](2) in the control source of the text box but nothing is showing when I select an item in the ComboBox

SELECT t_Manufacturer.Manufacturer, t_Manufacturer.Country
FROM t_Manufacturer
ORDER BY t_Manufacturer.[Manufacturer];
0
Hello experts

I have a field called MRN in a table called insurance  that contains data
A-000-622-999 in the MRN field.

I want to delete  The first 6 characters and the dash in the middle between the 2 and the 9.

It should be 622999

Any help please
0
I created a field in a table called attachment. This filed is for attaching PDFs or Excel files. I was able to attach one pdf and open it. Can I attach two pdfs? If yes, how do I select which pdf to open?
0
Hello all.

I have code to search a MS Word Document.  That code works fine.  It finds all "TBD" and I store them and other data in a MS Access Table.

What I need:
When the "TBD" is found, I need code to select and capture the entire sentence in a variable.   See example sentence below.
I am doing this from MS Access.

TABLE 4.5.1 SHOWS THAT CAPTAIN AMERICA CAN LIFT THOR'S HAMMER AND IT IS TBD IF ANYONE ELSE CAN.

strCaputuredSentece = "TABLE 4.5.1 SHOWS THAT CAPTAIN AMERICA CAN LIFT THOR'S HAMMER AND IT IS TBD IF ANYONE ELSE CAN.."

Thanks
WonHop
0
I have 2 queries which are listed below.

I need a query that will subtract the result of the second query from the result of the first

Query 1:

SELECT sum(AMOUNT_FIELD) AS AMOUNT_TOTAL
FROM Source;

Query 2:

SELECT sum(AMOUNT_FIELD) AS AMOUNT_TOTAL
FROM Target;
0
I have an Access table named MyTable with 10 fields.   They are:

Field1
Field2
ORDER_ID_Field
Field3
Field4
Field5
Field6
Field7
Field8
AMOUNT_Field
Field10

The table contains some instances of duplicate and triplicate records which can be identified by the values in Field10. In other
words, it is the values in Field10 only that for my purposes determines whether a record is a duplicate or not.

The values in the ORDER_ID_Field are unique.

Not sure if this can be done in a single query but what I'm looking for is the dollar difference between the SUM of all records in the table and the SUM of all records in the table excluding multiple instances of the same record.  

So, for example, just using three fields to illustrate, let's say ORDER_ID_Field,  AMOUNT_Field,  and Field10 contained the following:

ORDER_ID_Field   AMOUNT_Field         Field10          
1                                  $100                 Duplicate Record
2                                  $100                 Duplicate Record
3                                    $50                         Unique                              
4                                    $20                 Triplicate Record
5                                    $20                 Triplicate Record
6                                    $20                 Triplicate Record


The expected results would be   $140  (i.e. $310 total records - $170 single instance of records ). In other words, it …
0
I have an Access table named MyTable with 10 fields.   They are:

Field1
Field2
ORDER_ID_Field
Field3
Field4
Field5
Field6
Field7
Field8
AMOUNT_Field
Field10

The table contains some instances of duplicate and triplicate records which can be identified by the values in Field10. In other
words, it is the values in Field10 only that for my purposes determines whether a record is a duplicate or not.

The values in the ORDER_ID_Field are unique.

I need a query that will return the SUM of the AMOUNT_Field for all unique records as well as single instances of all records that
occur more than once (i.e. where a value in Field10 occurs more than once).

So, for example, just using three fields to illustrate, let's say ORDER_ID_Field,  AMOUNT_Field,  and Field10 contained the following:

ORDER_ID_Field   AMOUNT_Field         Field10          
1                                  $100                 Duplicate Record
2                                  $100                 Duplicate Record
3                                    $50                         Unique                              
4                                    $20                 Triplicate Record
5                                    $20                 Triplicate Record
6                                    $20                 Triplicate Record


The expected results would be $170  (i.e. one instance of "Duplicate Record", one instance of "Unique" and one
instance of "Triplicate Record"…
0
I have a Form with a SubForm.

I'm trying to provide a reference to a SubForm that is contained within a Main Form.  The SubForm is not linked to the Main Form.

I have copied the Form Names directly from the Objects so they match exactly.
'Main Form Object Name: F-48-910 - Create New Event Import Data Form
'SubForm Object Name: F-48-911 - Create New Event Import Data SubForm

Code
Dim F48911_001 As Form
Set F48911_001 = [Forms]![F-48-910 - Create New Event Import Data Form]![F-48-911 - Create New Event Import Data SubForm].[Form]

When the 'Set' statement runs I get an Access Error Number 2465  'Can't find the field 'F-48-911 - Create New Event Import Data Subform' referred to in your expression(2465).

I have tried for a couple of hours to fiugure this out without solving it.

Please help!

Thanks,
Bob C.
0
Hi

I have to split an old database and then apply table name changes to the backend Access file that is created and then hook it in to a new front end. This involves table name changes and addition of columns. I have to do this very quickly online during a conference  call. My plan is to split the old database and then run VBA code in the new backend to change the table names etc. Does this sound like a good approach?

Thanks
0
I want to do a cross tab report query in Microsoft Access but error message says won't let me.

The error when I try to go through the wizard is shown in this image attachment.
I've also included the Microsoft Access database that contains the data and the final Excel report I'm trying to build as an example.

Basically working off the query:  qDataCombined I want to build a report that shows whether or not a Tag (normalized in table:  usgaaptags) is assigned to a company (normalized as table:  usgaapcompanynames.  

The structure is described in the Excel and basically just puts a 'y' for the value of that Company.

Two questions:
How would I build a Microsoft Access query that would look exactly like my Excel attachment from this MS Access database?
Why does the crosstab query doesn't work in this situation?
ee-example-of-cross-tab-not-wokring.PNG
Final-report.xlsx
ee-tag-example.accdb
0
I'm having a hard time understanding MS SQL Express.

I'm an MS Access developer and I currently have many apps that connect to SQL server. But my understanding in setting up my own version is limited.

I've downloaded SQL Express 2017, which seems to be a 64 bit edition. But it seems that now I have several versions of SQL Express. Take a look at the Configuration Console:

SQL-Issues-01.jpg
As you can see, the only one running is SQLExpress01. But I don't know what version of SQL this is. Is there a way to tell? Also, is that the latest one?

it loads from "C:\Program Files\Microsoft SQL Server\MSSQL14.SQLEXPRESS01\MSSQL\Binn\sqlservr.exe" -sSQLEXPRESS01 which looks like MSSQL Version 14. Why isn't it 17?

The other ones are even older version like MSSQL11.

Using SQL Server Migration Assistant I was able to migrate an old mdb over to SQL, but I can't call up the design in the Management Studio. I get this message when I right click on a table and try and get the design:

SQL-Issues-02.jpg
Why don't I have the ability to update the tables through Management Studio?

Any help or resources to help me understand these issues would be greatly appreciated.

Thanks.
0
image of error - part 1 How to fix run-time error '3075' syntax error (missing operator in query expression in a SQL INSERT statement within Microsoft Access?

please note attached image that describes the error:

the yellow highlights on line item with Currentdb.Execute sSQL

sSQL = "SELECT [tagID], [usgaapfilesid] FROM [usgaapfileswtags] WHERE [tagID] = " & lTagID & " AND [usgaapfilesid] = " & lIDFileName
      Set rsEnsure = CurrentDb.OpenRecordset(sSQL)
      bNotFoundData = rsEnsure.EOF
      rsEnsure.Close
      Set rsEnsure = Nothing
      
      If bNotFoundData = True Then
       
        sLineItem = Replace(sLineItem, Chr(34), "''")
        
        sSQL = "INSERT INTO [usgaapfileswtags] ([tagID], [usgaapfilesid], [usgaaprow]) VALUES (" & lTagID & ", " & lIDFileName & ", '" & sLineItem & "')"
        CurrentDb.Execute sSQL
        DoEvents
      End If

Open in new window


The following variable's sLineItem  value is the cause of the issue:  
<us-gaap:EarningsPerShareTextBlock id=''ID_6'' contextRef=''FROM_Jan01_2018_TO_Dec31_2018_Entity_0000034088''>&lt;div&gt;&lt;p style='text-align:justify;margin-top:0pt;margin-bottom:0pt;line-height:12pt;' &gt;&lt;font style='font-family:Times New Roman;font-size:10pt;font-weight:bold;margin-left:0pt;' &gt;1&lt;/font&gt;&lt;font style='font-family:Times New Roman;font-size:10pt;font-weight:bold;' &gt;2&lt;/font&gt;&lt;font style='font-family:Times New Roman;font-size:10pt;font-weight:bold;' &gt;. Earnings 

Open in new window

0
I just noticed that our database's largest table has two indexes that appear to do nearly the same thing:

Index Name    Field Name  Primary Unique IgnoreNulls
PrimaryKey     KeyUID          Yes        Yes      No
IndexKeyUID   KeyUID         No         No       No

I am tempted to delete IndexKeyUID.  Is that a bad idea?

In fact, I am not sure I know if the index name really matters.  Could it be called "George" and still accomplish the same thing?
0
I'm trying to split a full name field LastName, FirstName or LastName, FirstName Initial or LastName, FirstName Initial. (period). into FirstName and LastName fields, If there is initial I want to disregard.
Examples:
Smith, John
Smith, John A
Smith, John A.
Smith, John JR
0
What Access VBA code would I use to import all the tables that I created in another database into the current database with all the data unchanged
0

Microsoft Access

221K

Solutions

52K

Contributors

Microsoft Access is a rapid application development (RAD) relational database tool. Access can be used for both desktop and web-based applications, and uses VBA (Visual Basic for Applications) as its coding language.