Hi,
My database has 3 fields ID,Cust_Balance,Cust_Type.
I need to assign Cust_Type as 1,2,or 3 based on Cust_Balance amount.
If Cust_balance is lessthan 3 million then Cust_Type is 1,if between 3 and
10 million then type 2 and,more than 10 million as type 3.How do i accomplish this in Access ?Is IIf the right choice?:confused:
I have a query based around a persons sick start and return date. A report is based upon this and then this makes up a sub part of management information report that is linked through a formatted month. Part of my report is based around the following formula, this governs which MI report to include it in
repMonth: (IIf(IsNull([returndate]),Format(Now(),"mm/yy"),Format([returndate],"mm/yy")))
This states that if a person has not returned to work then give them a formatted version of now() so it can be linked to the main report. My problem is if i wanted to look at a previous months report and a person has still not returned to work they will have the now() date and thus not be included in a previous months report! Does this make sense, have i gone a really out of the way route to solve my problem and can anyone see a more effective way of carrying out this problem?
thanks in advance, sorry for confussion!
Heya,
I was working on an sql problem and solved it:
SELECT planten.PLANTNAAM, planten.SOORT
FROM PLANTEN AS planten
WHERE (((planten.ART_CODE) Not In
(SELECT offertes.ART_CO FROM OFFERTES offertes, BESTELRG bestelrg, BESTEL bestel
WHERE offertes.ART_CO_LEV = bestelrg.ART_CO_LEV
AND bestel.Bestelnr=bestelrg.bestelnr
AND bestel.Lev_code=offertes.lev_code)));
However, I need find an alternative to NOT IN if one exists and isn't too long? if anybody can offer an alternative it would be appreciated :)
My company has satelite offices, which will be using database as well. For security and performance reasons, it is not feasible to have them connect to central server for a central back-end. (This may be different if we used a SQL backend, but migration takes time so that will need to wait).
My idea was I'd provide each satelite office their own local backend and have backend transmit the data to a central backend on a server which is fully replicated periodically while not in business. This would give the best balance between performance, security and network traffic.
But that creates the problem of possible conflicts between records' keys. I'm looking at GUID, and based on the amounts of records we usually do, I'm thinking GUID is a bit too excessive.
Maybe anyone can make suggestions if there's another way to prevent key conflict when the local backend and central backend synchronizes?
Hi,
I need a natural unique id for current and new enrollees, and I do not want to store social security numbers. If a new person enrolls, I need a way to know if they're already in our system.
I was wondering if a lookup table would work, where you switch a random autonumber for a social. If this will work, is it more secure than simply storing the socials?
Thank you!
I have a database with with 100s of values for a field. What I would like to be do is specify a value via a form and the query will return all options that equal the specified value.
so for example: Specified value = 150
Field 1 Field 2
------- -------
Name1 10
Name2 50
Name3 25
Name4 25
Name5 100
Name6 40
Name7 125
Name8 50
I'd like the query to return all possibilities that equal 150 so it should look like:
result 1 - name5 + name2
result 2 - Name5 + Name 8
Hi,
I've got an Access 97 piece that imports text files, copies and moves the used files and several other things.
I've now been asked to enable it to create a fairly complex folder structure.
I've done that, but I'd like to be able to issue a warning if the root folder already exists, can someone please show me how to check if a particular folder exists (I don't particularly care if its full or empty).
It was suggested to me to use a Not Exists Join to accomplish the below problem. I'm not sure how to write SQL for this.
I have the following:
1,000 records in table A
500 records in table B
Field ProductId is common to both tables.
If a ProductId from table A does not exist in B table a record needs to be added to table B. In this case 500 records from table A need to be added to table B.
Hello all. I'm trying to finish up a database for our farm and am running into issues as I'm pretty new to Access. I know VB6 but not a lot of VBA. We spray chemicals, and need to keep track of the acerage we spray for the date.
So here's my problem:
When I go to write my records, I need to search a table for a date. If the date exists already, then I need to update the total acreage related to the date. So first, how do I search the table and keep track of if the date was or wasnt found, and if found, calculate total acreage from value pulled from table, and the new acreage value, then write that back to the table.
Would really appreciate the help. Thanks.
Hi,
I have an access database with 8 tables and as many queries and forms. Our company uses an application that performs backups of other companies data, this data is imported into access into one table. The backups are scheduled to run overnight, but sometimes they do not start for whatever reason. We need to use a query to identify when a backup does not start, so therefore to identify when data does not appear or exist in the table.
This problem is only concerned with two tables. In one table i have the backups policies (Backup Policy) and the details about when the backup should run. In the other, the main table (Backup) is the actual data that has been imported. So far I have queried only the backup policies that should run, but i need to go one step further and compare this to data in the Backup table that does not exist.
Im not sure whether this should be a bit of code in the query that compares data in the Backup Policy table with that in the Backup table when it is not present.
Ideally i need something that says. "If a particular policy name does not appear in any record in the Backup table, report back a list of policy names."
OR
(Lookup a list of policy names in table backup policy,
if these policy names do not appear in the backup table, report back a list of the policy names,
if these policy names appear in the backup table, do not report back a list of the policy names)
If anyone has any ideas if this is possible, and how, I would be most grateful. Also, if it involves the use of code or functions etc I may need a little bit of guidance!!
Let me know if you need any more details of the table structures.
Hi all,
I have a form that is based on a query that uses a yes/no option as the filter. the problem i am having is that in this form if a user has more than 1 no entry the users name shows up as many times as entries that they have. i would like this to be shortened down to only one time for all no entries. is this done within the query?
SELECT Employee.EmployeeLastName, Employee.EmployeeFirstName, Employee.EmployeeID
FROM Employee INNER JOIN Expense ON Employee.EmployeeID = Expense.EmployeeID
WHERE (((Expense.AdminApproval)=No));
OR could this be done better in the on load of the form using a counting loop?
I need a function or way of dealing with a field that may or may not exist.
I am crosstabbing a large database and then building queries on the crosstab. However, the field from which the column headings come does not always have the same data in it as I have to cut the data in different ways (always similar but not always the same). As a result, sometimes I get the error message:
"The Microsoft Jet Engine doesnot recognise [FIELD] as a valid field name or expression"
where a field I was expecting did not appear.
If it was a null value within a field I remove it like this:
iif([Field] is null, 0, [Field])
Is there an equivalent formula for "exists" that will stop my queries falling over when it cannot find the field?
eg iif(exists([Field]),[Field],0)
Any help, guidance or assistance gratefully received!
I would like help with a macro that looks to see if a record in my database table exists, then if it doesn't it adds a record with my data, but if the records does exist, it stops the routine and sends a message box to prompt if you want to continue with adding a new record.
The problem that I'm having is that my macro errors out when it doesn't find a record. I just want my macro to just see if a record exists or not. Then I can write my Iff statements based on whether it finds our or not. How do I just have my macro check for an record, then if it doesn't exist tell that it doesn't exist and not stop my macro. Is there some kind of IfExist() function or something?
Thanks for any help anyone can give me.
Hi,
I put a Dlookup function to compare the new entry in a form and the code is like this:
If (Not IsNull(DLookup("[empno]", "Attnd_tbl" , "[empno]=[empnotxt]")))then
MsgBox "This Employee Already Exists"
cancel = true
Me!empnotxt.undo
end if
end sub
this works for comparing one field only, since I want to check if the employee is already entered on a certain date, how do I put two fields in this function to be looked up if for example "[empno]and[date]","Attnd_tbl","[empno]and[date]=[empnotxt]and[datetxt]"
I'm using the following code to autofill the city and state on my form.
I can't figure out how to capture if there is no matching record in the table.
If there is no matching record, I will:
1) inform the user with a msgbox
then if the user wants
2) open a form to add the record to the table
Dim rst As DAO.Recordset
Dim db As DAO.Database
Set db = CurrentDb
Set rst = db.OpenRecordset("tblZipcodes", dbOpenTable)
rst.MoveLast
rst.MoveFirst
Do Until rst.EOF
If strZipLookup = rst!ZipCode Then
Me.txtCoCity = rst![ZipCity]
Me.cboCoSt = rst![ZipState]
End If
rst.MoveNext
Loop
rst.Close
End If
How can I capture if there is no matching record in the table?
Thanks,
Sup
I have set up a simplified event booking procedure, similar to the one generated by the access event manager wizard. It uses three tables, one with attendees details, one with the event details and abooking table that links the attendee ID with the Event ID.
The booking table uses commands similar to SELECT[Names + Contacts].[ID],[Names + Contacts].[Surname]FROM[Names + Contacts] to generate drop down lists of surnames and events so that the two can be matched together.
This works fine as a table.
However when placed into a form, whenever the form is opened I get an error message saying the record source specified on the form does not exist.
I have tried regenerating the form, etc. I have used a wizard to generate the form, closed the form, not changed anything in the database and then tried reopening it and I get the error message, can anyone shed any light?
Also if you accept the message then it knows how many records there are, so it must know there's something behind it, also if you change to design mode and then back to form mode, it finds all the data.
I 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 RelatedHello 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! :)
I have a training database with multiple courses. Certain staff do course1 and thereafter course2. Course1 is only done once and course2 is a refresher done annually. I want the query to find staff that have done course2 but not course1.
Thanks.
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
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.
Afternoon.
I have had a search through the forum however am unable to find anything...
I have two questions:
a) How do I check whether someone else is currently accessing a record / form?
i.e. Opening the form (SchemeDetails) like this:
DoCmd.OpenForm "SchemeDetails", acNormal, "", "", acEdit, acNormal
Forms!SchemeDetails.SetFocus
DoCmd.GoToRecord acForm, "SchemeDetails", acGoTo, tempVal
Can I check if someone else is in the specified record before I open it? Record Locking is (deliberately) set to "No Locks".
b) How do I check to see if a table exists?
i.e. If DataTable2.Exists = True then ...
Or something like this?
Hope someone can help me!
I have a database with 200.000 records, how I Add Primary Key Field.
I'm getting error "File sharing count exceeded..", tried to increase "MaxLocksPerFile" registry , but without success.
My problem is if there is no FaultTotals nothing shows up. I would still like for the Four Columns to show data and have a 0 in the FaultTotals.
How can I accomplish this?
In a nutshell I am totaling all Cosmetic Faults based on the SystemGroup CTWT and a Date Range. If there are
no Cosmetic CTWT Faults for the DateRange I want a zero. The query works great as long as there is at least
One FaultTotal for the Date Range.
SELECT "1-3" AS Truck, "Cosmetic" AS Category, WorkUnitsFaultsMainTBL.SystemGroup, Count(*) AS FaultTotals
FROM WorkUnitsFaultsMainTBL
WHERE (((WorkUnitsFaultsMainTBL.FaultCategory)="Cosmetic") AND ((WorkUnitsFaultsMainTBL.TodaysDate)
Between [Forms]![Queries_ReportsFRM]![StartDateTxt] And [Forms]![Queries_ReportsFRM]![EndDateTxt]) AND ((WorkUnitsFaultsMainTBL.BuildID) In ("E010","C809","F001","C810","F187","A910","M173","M174")))
GROUP BY WorkUnitsFaultsMainTBL.SystemGroup
HAVING (((WorkUnitsFaultsMainTBL.SystemGroup)="CtWT"))
ORDER BY Count(*) DESC;
I have a table that is filled based on selections that a user makes in combo boxes on a form. I am having trouble trying to figure out the best way to prevent duplicates from being inserted in the table when I do the append query part.
Since the table is filled based on the various selections the user makes I dont have a primary key or unique field.
Can someone help me figure out the best way to do this append/update queries to prevent duplicates. Also if you have a strategy for setting up some sort of unique or primary key for fields that could all be the same yet in the table only 1 record will show, no duplicates.
Hi
I have to rethink and change the way of display in my form.
I have a form bound to ID.
I want to show 2 fields if they contain data, otherwise hide them
How do you do that?
Micke