I have a file that I want to import on a daily basis and append to an existing table in my database. The date changes each day. I want to create a query that checks to see if the date (of the first record) already exists before I import and append the new file. If it does, I want to show a message saying something to the effect of "This file has already been imported".
View RepliesI have a file that I want to import on a daily basis and append to an existing table in my database. The date changes each day. I want to create a query that checks to see if the date (of the first record) already exists before I import and append the new file. If it does, I want to show a message saying something to the effect of "This file has already been imported".
View 12 Replies View RelatedHi Guys!
Need help in putting up SQL string.
I have two tables. The first one contains customer information. Primary key is custno. The second one contains customer logins, primary key is also custno.
What I want to do is to view customer information that does not have customer logins.
Is it possible to do in MS Access 2003?
Regards,
Aga
Hello all
I had a query which was working fine to tell me the number of weekend bookings for holiday parks for a specified year:
SELECT Bookinfx.[Park Name], Month([Start Date]) AS FilterMonth, Year([Start Date]) AS FilterYear, Count(Bookinfx.[Park Name]) AS Bookings
FROM Bookinfx
GROUP BY Bookinfx.[Park Name], Month([Start Date]), Year([Start Date])
HAVING (((Year([Start Date]))=2005));
However it has become necessary to only find bookings of specific accommodation types so I modified it like so:
SELECT Bookinfx.[Park Name], Month([Start Date]) AS FilterMonth, Year([Start Date]) AS FilterYear, Count(Bookinfx.[Park Name]) AS Bookings
FROM Bookinfx
WHERE ((Bookinfx.Day)="FRI" Or (Bookinfx.Day)="SAT") And ((Bookinfx.Nights)<=3) And ((Bookingfx.[Accom Type])="CHALET/LODGE" Or (Bookinfx.[Accom Type])="STATIC VAN")
GROUP BY Bookinfx.[Park Name], Month([Start Date]), Year([Start Date])
HAVING (((Year([Start Date]))=2005));
Now when I run this, the first problem is that it asks me for the accommodation type, which is odd as I've already specified this in the query. The second problem is that if I enter say 'chalet/lodge' it returns records telling me that there are bookings for chalets and lodges at parks that only have camping! I have trawled through the database to make sure no erroneous records exist and so I know it is a problem with the query.
If anyone can shed any light on what I've done wrong, I would be extremely grateful! :)
Hi,
I'm trying to have a button on a form that would delete a record from a table only if there are no child records linked to it.
If there are child records for the selected record, the button would be disabled.
Is there a simple way of doing this?
TIA.
What could cause certain records to not be searchable when performing a search?
I have created a pretty simple Search Form that I can look up a record by a Job#, Phone#, or Name. It populates a list of record(s) on the bottom pane using a split-form.
The issue that I have recently discovered is that some records are not showing up when searched, though the record does exist. I can find the record by manually going to a record in the database that is close to the one I’m looking for and then using the previous and next record button to view the record I want.
So if a record exist and has the relevant data for the field that is being searched, why would it not find it? Using the default search built into Access on the bottom record bar, I can find the record using the search criteria I mentioned above.
I also have the Search Form set that if no search criteria is entered, it will display ALL records. Even with all the records shown, these few records do not show up.
Could these few records have a feature or setting that is disabled that needs changed? If so, what to look for at this point.
I have a query that runs just fine and takes about 1 minute to run on average. I have vba code that uses Dlookup to determine if the query returns any records. The problem is that each time it runs it has to crunch all the data, all I care about is if there is any result at all. Is there a way to get the query to stop after it finds one record to speed things up greatly?
View 2 Replies View RelatedI have a main form with 3 subforms. Each subform is identical except for the value of the filter property. The filter is for the same field, but with a different value for each subform. So, for example, the first subform has a filter of:
Code:
[WBS Element]="DEF" And [Period]=Forms!frm_ProjectFinancials!Period
while the second subform has a filter of:
Code:
[WBS Element]="PPE" And [Period]=Forms!frm_ProjectFinancials!Period
and the third subform has a filter of:
Code:
[WBS Element]="EXP" And [Period]=Forms!frm_ProjectFinancials!Period
The recordset for each subform results in a single record with numeric values in each field or no records at all. When the resulting recordset is empty (no records), the bound text fields on the subform display as blank. I want these fields to display 0 instead of blank so I can use them in other calculated fields. Functions such as Nz or IsNumeric do not work since there are no records and the values are neither null nor numeric.
How I can display zeroes in the bound fields when no records exist that meet the filter criteria? Or is there a way that I can dummy a resulting recordset to have all zero values when there would otherwise be no records?
I need to find all MaintItems which field MIC don't exist in either MIC1 or MIC2. So the only result which I need to see is MaintItem '5100161086' as MIC <> MIC1 Unfortunately I can create query which would approve '5100662734' as well
MaintItem ------MIC------ MIC1 ------ MIC2
5100634036 50000173 50000173
5100662790 50000174 50000174
5100662734 50000145 50000145 50000180
5100662734 50000180 50000145 50000180
5100161086 500001711 50000173
So bottom line I need a query which will display only the row below as MIC doesn't exist in neither MIC1 nore MIC2. Also I will have to deal with much more MICs soon so maybe there is a way to concatenate them and compare, isn't it? 5100161086 500001711 50000173
Attached query is what I got so far, but that displays last 3 rows of table above
I'm trying to make a query to return payroll data, but I'm not sure how to get Access to return 0 where data doesn't exist for a particular month.
For example, imagine this table:
Name MonthHours
John 1 160
John 2 160
Dave 1 160
Dave 2 160
Matt 1 160
How would I get this ouput:
Name MonthHours
John 1 160
John 2 160
Dave 1 160
Dave 2 160
Matt 1 160
Matt 2 0
I have a parent form which has a yes/no checkbox in the form's record source. Then in that parent form I have a sub-form. If NO records exist in the sub-form I want the checkbox to be UN checked. If records DO exist in the sub-form, I want the checkbox to be CHECKED.
But I want this to happen as records are added or deleted from the sub-form. In other words, if the parent form is opened and no records exist in the subform then the checkbox should be unchecked. But as soon as the first record has been entered in the subform, the checkbox on the parent form should be checked. Likewise, as soon as the last record has been revoved from the sub-form, the checkbox on the parent form should be unchecked.
What code do I need to accomplish all of this?
All of the types? Including Memo?
View 2 Replies View RelatedUsing Access 2000
I have a field which lists a date. I would like to determine which financial month the date is.
However the financial month starts and ends at odd times, for example
The start time of the month is the first Sunday after the last Friday of each month. With the end date being the day before this.
So for July it would have been
30/06/2013 - 03/08/2013
and August is
04/08/2013 - 31/08/2013
I would like the output to show something like "July 2013"
How to do this?
I am looking for a query that will return records from a table that have related records in another table. Opposite to the Unmatched Query Wizard.I have two tables: tblSupplier and tblSupplierProducts.The two tables are related by the field "SupplierId".I need the query to only return Suppliers that have Products.
View 3 Replies View RelatedI am trying to import data from excel into one of Access table but it showing error F30 does not exist in destination table. I have checked headings which are matched. Same sheet tried to import in access successfully, but unable to import specific table...
View 1 Replies View RelatedI have a query that displays the average call rate on various pieces of equipment over the last 12 months. I was wondering if there is a way to have Access look at this query and determine if the call rates are trending up or down over that time frame. Kind of like a trend line would do on a chart in Excel, only I want this to show me the word Up or Down in my results. Is this possible?
Here is the query I am using:
Code:
TRANSFORM FormatNumber(Avg([All Call Rate]),2) AS AvgCallRate
SELECT List_With_TNC_tbl.Device, List_With_TNC_tbl.Model, List_With_TNC_tbl.[Item Num]
FROM List_With_TNC_tbl
GROUP BY List_With_TNC_tbl.Device, List_With_TNC_tbl.Model, List_With_TNC_tbl.[Item Num]
PIVOT Format([Month Start Date],"mmm") In ("Jan","Feb","Mar","Apr","May", "Jun", "Jul","Aug","Sep", "Oct", "Nov", "Dec");
I have a multiuser database back end with approximately 25 tables. The file size for the back end has recently jumped from approx. 50mb to 270mb, but I do not know where the data causing this increase is placed. Is it possible to measure the file space usage (in mb) that each table contributes so that I can determine the source of the increase.
View 1 Replies View RelatedThe field SECL DDI has the users phone number unfortunately over time these have been entered in different formats so there are 5 digits, 6 digits, 7 digits etc...Can I run a query that counts the number of digits in each filed and then tell me how many of each exist
Ie
4 digits 3412
5 digits 5000
How would i write an if statemet that does the following:
If Forms!Form1!Field1 exists in table1!Field1 then run Query1
Let me know,
Thanks,
ovadoggvo
I have a survey database that I've been using for the last year for monthly auditing of employees files. I need to be able to get monthly audit scores for each employee but grouped by their manager. The problem I'm having is employees have moved between managers throughout the year, so employees that are listed under Manager 2 now were actually working for Manager 1 when the audits occurred.
ie. Audits occurred Jan - April for Employee 1 while they were assigned to Manager 1. Employee 1 moved to Manager 2s team in May. So when running monthly reports for the year Employee 1 audits should fall under Manager 1 for Jan-April and Manager 2 for May-Dec.
I do have a history table set up like:
tblEmployeeHistory
ID (PK)
EmployeeID (FK to Employee table)
ManagerID (FK to Manager table)
MoveDate (date employee assigned to manager)
The Employee table is set up like:
Employee ID (PK)
EmployeeName
ManagerID (FK to Manager table)
The Manager table has the ManagerID and ManagerName.
That's the employee side of things; then I have the tables that store the audit results:
tblAudit
AuditID
FileNumber
AuditDate
EmployeeID (FK to employee table)
tblAuditResults
AuditID; QstnID (Composite PK, QstnID is FK to tblQuestions)
Answer
How can I use AuditDate and MoveDate to relate audits to the managers the employees were under when the audits occurred?
I have a very simple query to determine the gender ratio of an associations membership. My SQL code neatly calculates the number of females, viz
SELECT [Mail List].[GENDER], Count([Mail List].[GENDER]) AS TOTAL
FROM [Mail List]
WHERE ((([Mail List].[GENDER])="F"))
GROUP BY [Mail List].GENDER;
However, I wish to present this result as a percentage of total membership.
My main Table has a column titled [Member Name] so my requirement is to produce a calculation of the form "Females"/"Member Name Total" all multipliied by 100.
"Field 'F1' doesn't exist in destination table 'tablename.'"
I hate this error message.
I am using the following command to load data from an excel spreadsheet into a backend SQL Server database via an .adp:
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel7, sTempTable, strFileName, False, "A2:B4000"
I have purposely used "False" to ensure that the first line in my spreadsheet is ignored. This is because the first line in my spreadsheet contains headings that do not match the column names in my table.
I do not wish to change my headings as end users will be making use of my application and they will not like headings such as "int_FactoryID". Likewise I do not want to change the column names in my table to words such as "Factory ID" as this would be a bad naming convention.
Is there a way to use TransferSpreadsheet without necessarily matching the headings in the spreadsheet to the column headings?
Is there a way for TransferSpreadsheet to ignore the headings and assume that the first column in the spreadsheet needs to go to the first column in my SQL Server table?
Any help would be appreciated.
Thanks
Kabir
Validating field from a query. I have a table with a field that has a value number that I need to validate that that number exist in another table in a field
Table1.field1 Number
Table2.field1 number
So let's say a have in table2.field1 the list 1 2 3 4 5 8
In table1.field1 I need to validate that the number I enter is present in table2.field1 so 1 would be ok but 6 invalid and it can't be a from list statement because I need the person to enter a number and get no error or get invalid number.
I am writing code to Refresh table links. I only want to refresh the Access table links and ignore the Excel Table links. Is there a way to determine in VBA if the table link is pointing to an Excel file?
View 8 Replies View RelatedI'm having trouble viewing values that exist in table 'tblTest2' to the user. The first set of code (below) doesn NOT work. Yet the second set of code (all the way at the bottom) DOES work. And by work I mean that it erases the old values and only puts the tblTest2's new values. The first set of code has values but they are not the right ones. The second set of code has the correct values for its statement...
Code:
strTable = "tblTest2" 'Table for storing values for viewing purposes
DoCmd.DeleteObject acTable, strTable 'Delete tblTest2 old values
CurrentDb.Execute "CREATE TABLE tblTest2 " _
[Code].....
I'm trying to write some Code with the target to take a value from a textbox (out of a form) and a pregiven value (in this case = 2) and enter them in a new record in a table. But only if there is not already a record with the exact same combination of these to values. When done, the same button should proceed you to the next form (but this code is not already implemented in the fragment below) even if no values are added because they already exist in this combination.
Code:
Public Function GetID_PatientStudiesGroup&()
GetID_PatientStudiesGroup = [Forms]![frm_PatientStudiesGroupTZP]![ID_PatientStudiesGroup]
End Function
Private Sub BPRS_T1_Button_Click()
Call GetID_PatientStudiesGroup&
Dim strSQL As String
[Code] ....
As you can see, I tried to use a function to refer to the value out of the form, because I didn't find out how to refer to a form in an SQL Code.
Just to give you a better overview: The table where the information should be added is named "tbl_PatientStudiesGroupTZP". The form where you find the Textbox "ID_PatientStudiesGroup" (with the value I need to transport) is named "frm_PatientStudiesGroupTZP". Every part (except the WHERE NOT EXISTS part) worked perfectly for itself, but not when thrown together.