I have a large table with millions of entries. I want to run a query to return all entries that are on a Saturday. The date stored in the table though is just typical date format eg 15/11/2015.
View RepliesI was just wondering if this is a possibility to do in one query or if it has to be run from a number of different queries.
I am currently developing a database from scratch for work (with very little Access experience).
The current query I am trying to run, if linked to a number of tables with different information.
What I am trying to do primarily is link stock to a specific "Host Name", "Serial Number" and "Part Description".
In the "Host Name" there is for example - A1-TX10-10001, B1-TX2-10004, C1-TX-10004 - The latter part of the name is a unique identifier number. The first part is the compartment in which the "stock" sits. So you may have all three components (A1-TX1, B1-TX2, C1-TX3) linked to the same unique identifier (10001 for example)
The serial numbers naturally are different for every single one and of course the srial numbers are linked to the "Part Description" - which will read something like....."C1-TX3 Transmitter", "B1-TX2 Combiner" etc.....
When I run the query like this the Host Name (which is also linked to the unique identifier on its own (10001) it returns everything under "A1-TX1-10001"
What I would ideally like to do is write a statement so that if the "Part Description" contains "A1-TX1" it will only return rows that contain "A1-TX1" in the Host Name and the same for "B1-TX2" and "C1-TX3" in the same query.
If "Host Name" contains "A1-TX1" to return "Part Description" to contain "A1-TX1"
I have a subformClientOffers on my frmAllProperties. On the subformClientOffers is a field called Outside Agents. This field allows for multiple values to be selected.
The subfrmClientOffers is in datasheet view.
While the field Outside Agents DOES allow me to select more than one, they post in the field one after another:
Ken Shaw, John Doe, Jane Doe, Harlan Bestlyn
Therefore, the datasheet view of the subformClientOffers causes the field Outside Agents to get very long, much too long to view without scrolling. My client wishes for everything on the frmAllProperties to be viewable without scrolling.
Is there anyway, to cause the field Outside Agents to place a return between each selected name (sort of a wrap text kind of thing)?
One thing I might mention is that the field is a drop down field from tblOutsideAgents. The table itself has First Name and Last Name, but I concatenated that into Fullname on the form, so that when the user opens the drop down they see the entire name. I don't know if that would have any effect on making the 'return' thing work or not.
I can't use this field as a subdatasheet because I already have one subdatasheet attached to the subform to show multiple notes on each Client Offer.
I have following SQL statement:
SELECT TOP 10 tblPortCallList.Port, tblPortCallList.Arr_Date, tblPortCallList.Dep_Date, tblPortCallList.Security_Level_Ship, tblPortCallList.Security_Level_Port, *
FROM tblPortCallList
ORDER BY tblPortCallList.Arr_Date DESC , tblPortCallList.Dep_Date DESC;
As shown, it picks the top 10 entries in a table.
Is there a statement, which can choose specific entries in a table, in stead of the top 10? E.g. entry number 2 in a table? I have three text boxes, which need to show the previous port of call, arrival date and departure date. The table in question is sorted after arrival date and then departure date.
Thanks.
For my database the business has Clients who they conduct Needs Assessments for, but they only conduct 3 needs assessments. I have a Needs Assessment table with Client ID as the foreign key. how I can limit the number of times a Client ID shows up in the foreign key field to three?
View 2 Replies View RelatedHaving problems with the following function to goto a specific record and return a value from a field in that record.
Code:'Public Function intFieldZConv(strICAO As String) As IntegerPrivate Function intFieldZConv() As Integer 'TEST PURPOSE ONLYDim strICAO As String 'TEST PURPOSE ONLYstrICAO = "KTCM" 'TEST PURPOSE ONLYDim cnCurrent As ADODB.ConnectionDim rsFieldInfo As ADODB.RecordsetSet cnCurrent = CurrentProject.ConnectionSet rsFieldInfo = New ADODB.RecordsetrsFieldInfo.Open "tblFieldInfo", cnCurrent, , , adCmdTable'rsFieldInfo.Index = "FieldICAO"'rsFieldInfo.Seek "=", strICAOintFieldZConv = rsFieldInfo!FieldZConvSTMsgBox intFieldZConv 'TEST PURPOSE ONLYrsFieldInfo.ClosecnCurrent.CloseSet rsFieldInfo = NothingSet cnCurrent = NothingEnd Function
strICAO is received from another function which is to be used to determine the specific record in the table "tblFieldInfo". Once that record is referenced, the value from the field "FieldZConVST" is returned.
My problem area is highlighed (index and seek) which I receive an error stating "method or data member not found". Any help would be appreciated on how to finding a specific record.
Thanks
Jeff
OK so here is a working query:
SELECT Assets.*
FROM Assets
WHERE (((EXISTS
(SELECT *
FROM LCAMdump
WHERE Assets.BarcodeNumber = LCAMdump.T_TAG
)) =False))
OR (((EXISTS
(SELECT *
FROM LCAMdump
WHERE ((SELECT BuildingName
FROM Building_Names
WHERE ASSETS.BuildingNameID = Building_Names.BuildingNameID)=LCAMdump.BUILDING)
)) =False))
OR (((EXISTS
(SELECT *
FROM LCAMdump
WHERE ((Assets.FLOOR)=[LCAMdump]![FLOOR])
)) =False))
OR (((EXISTS
(SELECT *
FROM LCAMdump
WHERE ((Assets.DeskLocation)=[LCAMdump]![LOCATION_SEGMENT2])
)) =False))
OR (((EXISTS
(SELECT *
FROM LCAMdump
WHERE ((Assets.BuildingLocation)=[LCAMdump]![LOCATION_SEGMENT1])
)) =False))
OR (((EXISTS
(SELECT *
FROM LCAMdump
WHERE ((SELECT FirstName
FROM Employees
WHERE Assets.EmployeeID = Employees.EmployeeID)=LCAMdump.USER_FIRST)
)) =False))
OR (((EXISTS
(SELECT *
FROM LCAMdump
WHERE ((SELECT LastName
FROM Employees
WHERE Assets.EmployeeID = Employees.EmployeeID)=LCAMdump.USER_LAST)
)) =False))
OR (((EXISTS
(SELECT *
FROM LCAMdump
WHERE ((SELECT SSO
FROM Employees
WHERE Assets.EmployeeID = Employees.EmployeeID)=LCAMdump.LOGIN_SSO)
)) =False))
OR (((EXISTS
(SELECT *
FROM LCAMdump
WHERE ((SELECT UserID
FROM Employees
WHERE Assets.EmployeeID = Employees.EmployeeID)=LCAMdump.USER_LOGIN)
)) =False));
It works great returns the correct results. But I don't need everything out of Assets. I just need a few things from there and a few things from 2 other tables.
I tried this but it now gives back over 220 repeating results.
SELECT Assets.BarcodeNumber ,
Employees.UserID ,
Building_names.BuildingName,
Assets.Floor ,
Assets.BuildingLocation ,
Assets.DeskLocation ,
Employees.FirstName ,
Employees.LastName ,
Employees.SSO
FROM Assets ,
Employees,
Building_Names
WHERE (((EXISTS
(SELECT *
FROM LCAMdump
WHERE Assets.BarcodeNumber = LCAMdump.T_TAG
)) =False))
OR (((EXISTS
(SELECT *
FROM LCAMdump
WHERE ((SELECT BuildingName
FROM Building_Names
WHERE ASSETS.BuildingNameID = Building_Names.BuildingNameID)=LCAMdump.BUILDING)
)) =False))
OR (((EXISTS
(SELECT *
FROM LCAMdump
WHERE ((Assets.FLOOR)=[LCAMdump]![FLOOR])
)) =False))
OR (((EXISTS
(SELECT *
FROM LCAMdump
WHERE ((Assets.DeskLocation)=[LCAMdump]![LOCATION_SEGMENT2])
)) =False))
OR (((EXISTS
(SELECT *
FROM LCAMdump
WHERE ((Assets.BuildingLocation)=[LCAMdump]![LOCATION_SEGMENT1])
)) =False))
OR (((EXISTS
(SELECT *
FROM LCAMdump
WHERE ((SELECT FirstName
FROM Employees
WHERE Assets.EmployeeID = Employees.EmployeeID)=LCAMdump.USER_FIRST)
)) =False))
OR (((EXISTS
(SELECT *
FROM LCAMdump
WHERE ((SELECT LastName
FROM Employees
WHERE Assets.EmployeeID = Employees.EmployeeID)=LCAMdump.USER_LAST)
)) =False))
OR (((EXISTS
(SELECT *
FROM LCAMdump
WHERE ((SELECT SSO
FROM Employees
WHERE Assets.EmployeeID = Employees.EmployeeID)=LCAMdump.LOGIN_SSO)
)) =False))
OR (((EXISTS
(SELECT *
FROM LCAMdump
WHERE ((SELECT UserID
FROM Employees
WHERE Assets.EmployeeID = Employees.EmployeeID)=LCAMdump.USER_LOGIN)
)) =False));
I am sure it something simple but I am a novice at this so please help me. :D
what is the best/cleanest way to return the next primary key value which is in the format yyyy-###.These are case files for the current year, ### being 3 digit number incremented by one for each new case.
I could use a function that finds the last one, checks the date, increments by one and returns it, but maybe I could use a public property or a class? I'm keen to use classes more for code maintenance.With a class then: would i create a PrimaryKey variable of the class type/object, so when I need a new key I would just type PrimaryKey.NewKey, for example?
I have a Query of dates and I need to have those dates return onto a from horizontally?
View 1 Replies View RelatedI am trying to use a DLookup to search to find what block goes into the production of an item.
In a table called "dbo_vw_MCE_job_with_materials", each item contains a list of the parts which go into its production in a column titled "Expr1". However there are always more than one parts to this, with varying names.
What I am trying to do is to use a Dlookup to only return the first value which starts with B (as the block number is always the only part on the list which begins with B).
At the moment my DLookup would look ike this:
Code:
MixTypeTxt = DLookup("Expr1", "dbo_vw_MCE_job_with_materials", "[item] ='" & Forms![Theta Input]![ItemNumbertxt] & "'")
However I think as the item column contains the same item number in 8 columns, with different values in the Expr1 column, Dlookup only returns the top value "A-CF0057" (which is irrelevant as far as I am concered) - whereas I am hoping it will be able to pull the "B1499" value from the column as it is the only one starting with a B
Is there anyway to specify which value the dlookup function would return, or is there any way to apply some sort of permanent filter on my table?
Is there a way to find and remove duplicate entries in tables by using queries.....if so, how specifically?
View 1 Replies View RelatedI have a tblhealthrecord that has many multiple entries for the same animalID. Would like to pull up all records from this tbl for one animal. I can sort them later but just need to know what to ask for so I can read upon them. I at first thought this could be based on date but not really since you could have multiple entries in one day.
View 7 Replies View RelatedI have a query result-set with several columns: A, B, C ,D. All Four of these columns display numbers. From this query, which I will call the "SetUp"query, I want to query it and obtain the average for each of the columns A, B , C ,D. However, some of the values in columns A, B , C ,D are zero. If I use the standard 'Avg' function in Access, it averages the column AND INCLUDES THE ZEROS IN THE AVERAGE. How can I get an average of just all the non-zero entries?
View 4 Replies View RelatedHi All,
I am wanting to build a table which consists of all the queries and tables in the database.
I am expecting column 1 to have all the queries and column 2 to contain all the tables that make up the query. So the table will look like this:
Queries - Linked Tables
Query1 - Table1
Query2 - Table1
Query2 - Table2
Query3 - Table1
Query3 - Table4
As you can see a table can be linked to any number of tables.
Now for my actual question. I don't want to have to type in all the queries and tables. I would like to choose any query and table from a drop down of all possible queries and tables that are currently in the database - like a list, where the list contains all the possible values.
I would then select the relevant entry.
Any pointers would be great.
Thanks,
Evan
I have a query that feeds a report. This report shows a "Set" and the "Charge Numbers" associated with it. Each set could have a possibility of more than one set of IT numbers. This means there could be say 2 entries for set "D7" and IT numbers for both of them.
As a result when I run the query, and say the set has 2 Charge numbers, it will bring back 2 results, with 2 charge numbers on each of them, which is correct. So if a set had 4 charge numbers assigned to it, 4 results would show with 4 charge numbers for each result.
Is there any way I can make it only show one of those results as they are identical?
I know this is confusing, bare with me
I'm trying to use access to identify how many times an indiviual appears in my database. I've got a large file which gives me all referrals, each referral comes with two identifiers, one unique to the person, another unique to the referral. This means that the person unique number may be repeated multiple times (if a person has more than one referral) but the referral number would be different each time.
I've run a query to identify the duplicates, i.e. the records where the person unique identifier is repeated. What I am trying to do is work out now how many people have been referred twice, how many three times etc. The original query looks something like this (but on a much bigger scale).
Person Referral
1 A
1 B
1 C
2 D
2 E
3 F
3 G
3 H
What I'm after is a query that will tell me that two people were referred three times (1 and 3) and that one was referred twice (2) and so on.
I'm on Access 2010 if it makes any difference.
I have a form that feeds information to a query which in turn sets up a report. For clarity i will list out in basic terms what I have
Input Form
- Check Box to activate/De-activate a text field [chk-active]
- Text field for a parameter [txt-Parameter]
the query has a column that has null values from the originating table. These values will be added a t a later date, but need to be queried and reported at some business intervals.
In the query criteria for this column, I have the following Criteria
Like IIF([Forms]![ReportGenerator]![chk-Active]=0, "*", [Forms]![ReportGenerator]![txt-Parameter])[/I][/I]
I have tried for the last 5 hours to figure out how to write the formula to be able to get the blank entries to show up as well but have had no luck.
I was looking to run a query using one text box with multiple entries. For example : EP342, EP423, EP234. Is there a way to do this in VB?
View 5 Replies View RelatedIs there any way user can verify the data that its enter correctly, for example I want a button on user form, after every 14 entries when user hit that button it bring the sum of 14 entries.
View 5 Replies View RelatedI am looking up a product based on a unique code in tblProducts...I need to return one of two values
If the value of [Product type] = "multi" then return "multi"
else
If the value of [Product type] = "solo" then return the value of the non empty field in a range of fields
for example
tblProducts
PID (key) | PACKAGE TYPE | RED | YELLOW | BLUE
C13T0714010 | single | <empty>| yellow | <empty>
C13T0754010 | multi | red | yellow | blue
query
C13T0714010 returns "yellow'
C13T0754010 returns "multi'
I have a table called Stock Levels which contains 3 fields. (ID, ProductID, StockLevel) ID is the Pkey, ProductID contains duplicates and StockLevel which contains different stock levels
and I am trying to remove the duplicates and retain the the data so I am left with the correct stock number
what I have done is the following, but I am still getting duplicate values in productid and stocklevels
SELECT DISTINCTROW id, productid, stocklevel into mynewtable from stocklevels
I have attached a screenshot of the table
im in the middle of developing a database to log DOWNTIME of various machines in the factory. Basically myself and others will use it to log breakdown details and the amount of time (in HH:MM) the machine was off.
I have a form which will list details of a certain machine in a list box which i can populate using a query.
The list box will display 3 columns: Date of Breakdown, Machine Name and Downtime
I then have a text box below this that i would like to display the TOTAL amount of downtime for that machine (The listbox may have different information in it depending on the query criteria, e.g. It might display a certain month for a certain machine).
THE ISSUE i am having is its proving to be difficult to get the correct sum of the accumulative time to display correctly in the text box.
Is there a way i can use a query to calculate the total time of a column and return a value which is formatted as time (like this hhhh:mm or dd:hh:mm) or preferably use some VBA code on the form itself to total up the DOWNTIME column of the list box and display it with the correct format in the text
I have gotten close, but as the total amount of time exceeded 24 hours, it went past 00:00. I'm assuming it treated it as a clock and went into the next day? This is NOT what i am after, i need the total amount of days, hours spent on the machine and not a time of the day.
I have produced a label printing query which eliminates duplicate addresses from a mail list. It works beautifully.
However, when I try to replicate with a different field criteria the new query fails to produce any output at all.
The SQL code I am using is as follows:
SELECT a.[Member ID], a.[Address 1], a.[Member Name], a.[Address 2], a.Town, a.PostCode, a.[e-Mail List]
FROM [Mail List] AS a
GROUP BY a.[Member ID], a.[Address 1], a.[Member Name], a.[Address 2], a.Town, a.PostCode, a.[e-Mail List], CStr([a].[Member ID])+[a].[Address 1]
HAVING (((a.[e-Mail List]) Is Null) AND ((CStr([a].[Member ID])+[a].[Address 1])=(Select cStr(Min(b.[Member ID])) + b.[Address 1]
From [Mail List] as b
Where b.[Address 1] = a.[Address 1]
Group By [Address 1])));
The working version has 'HAVING' e-Mail News =False instead of e-Mail List is Null. The former searches for an empty check box and the latter for an empty field.
I know my way around Access reasonably well but am by no means an expert. I have created a system that I use in my business for hardware/software requests, and was told to do it in Access. I did ask that we do it on SQL Server with a Web Front End, but we are where we are.
Now, I have been on a SQL query training course so I know the basics, but am a bit confused on this one since Access has been added to the mix.To make matters more frightening, this is Access 2003!
My tables are as follows:
[T_Request]
AT Reference (primary key, autonumber)
Forename
Surname
UserID, etc etc
[T_Hardwarelist]
ID (primary key, autonumber)
Make
Model
Description
[T_UserHardware]
AT Reference
ID
[Request] is inner joined to [UserHardware] on the AT Reference column.
[UserHardware] is inner joined to [Hardwarelist] on the ID column.
The select query I have is basic and does just what it says on the tin; it shows who has what hardware.The query:
Code:
SELECT T_UserHardware.[AT Reference], T_UserHardware.ID, T_HardwareList.Make, T_HardwareList.Model, T_HardwareList.Type, T_HardwareList.Description
FROM (T_Requests INNER JOIN T_UserHardware ON T_Requests.[AT Reference] = T_UserHardware.[AT Reference]) INNER JOIN T_HardwareList ON T_UserHardware.ID = T_HardwareList.ID
WHERE (((T_UserHardware.[AT Reference])=[Forms]![F_Request]![AT Reference]));
However, when it comes to adding NEW hardware to the requestI have a form called F_Request.I have a sub form called SF_Hardware.The SF_ Hardware subform runs the aforementioned query, and shows what hardware is assigned to the parent request.If I add new hardware via a dropdown on the form, it adds it in to [T_UserHardware], but it also adds another value on the [T_Hardwarelist] table. E.g. if I add a "HP Compaq 8200 Elite" (which is stored in [T_Hardwarelist]) it adds it to the [T_UserHardware] table correctly, but creates a second (third, fourth, fifth) entry on the [T_Hardwarelist] table for the same kit.
I think its confusing because of using a select query and might have to run a 2nd query on save or something like that, I had a working version before that had the make, model etc in both tables and didn't have a 3-way relationship. It'd be nice to have the request table with the high level info, a hardwarelist table with our catalog of kit, and a userhardware table just containing the ID and Reference for the hardware/request rather than duplicating the information.
I have 3 tables;
tblEmployees - Stores employee data
tblProcess - Stores process data
tblTraining - Stores data on which employees have trained on which process
tblEmployees has a 1 to many relationship to tblTraining through empID
tblProcess has a 1 to many relationship to tblTraining through pcsID
When a new Process is added, a query auto populates tblTraining with 1 record for each employee in tblEmployees. Equally when a new Employee is added, a query auto populates tblTraining with 1 record for each Process in tblProcess.
In theory this should ensure that every employee has a training record for every process (and vice versa). However, as many of us know, never assume the user wont find a way to mess things up!
So i want to have a query that can show me Employee and Process without a training record. I have been manipulating a SELECT (SELECT) query to pull this together, but i just keep coming up blank.
I have attached a copy of the relevant tables and query. I have deliberately deleted a training record for empID 6 on pcsID 1 AND empID102 on pcsID 2 (qryCheckTrainingRecordsBalance will show the processes these 2 DO have a training record for).
I have a Membership List Table with a calculated field entitled Member Name.
The second table entitled Groups contains only 46 records each of which has up to 30 Fields also containing member names.
I Have written a query to establish which of the members from the single field of the first table do NOT featured in any of the fields and records of the second table. In the Query Design View I have listed 'Member name' from Table 1 followed by all the relevant member fields from Table 2, carrying the criteria 'Is Null'.
When I run the query, it merely lists all the members from Table 1.