I need help setting up some basic queries on my database, i dont really know what fields im meant to be querying so help would be great.
Sample data Included in attachment.
In the sample, seconds is the time step, so 5 minutes would be 300 seconds.
Queries i need to create:
Query 1 - Number of spill timesteps >0.001
Query 2 - Total number of timesteps
I'm attempting to create a very basic database for a school project. It is a property management database, and I am attempting to create a query that selects properties listed within the last 45 days. I'm unsure how to code this in the build field of the query.
Field: Date_Available
Table: Property_Table
Sort:
Show: (Ticked)
Criteria: (This is the part I am trying to code but am unfamiliar with the syntax)
what is wrong with the following statements. I'm new to access SQl. This would work in ms sql.
Code:
DELETE from Table1
INSERT INTO Table1(email, productid, datecreated, datesend) values ('adf', 5, '10/10/2012','10/10/2012')
Select * from Table1
I'm doing some debugging over the phone.My friend has Access 2003.What does not work:When he codes the MID function in a query he gets an error like this:"Undefined function 'Mid' in expression."
In the Visual Basic editor, he gets the same error: MID is undefined.What DOES work:I walked him through the use of LEFT and RIGHT in the query and they work.In Excel, MID works.Might there be some odd ball Access configuration mess up, or maybe more likely, his Access installation is incomplete.
I have 3 select queries which Im trying to output to a combo - Ive tried a UNION query but I get an error
ODBC-- call failed ODBC Driver SQLBase.....
Firstly is do the results need to match within a union query? I mean they have no relationship what so ever Im just trying to populate this combo with the same results.
Secondly is there a better way to do it? 2 of the select queries query a linked SQL table and the third is a local table. All of the select queries work on their own.
Hi All,
I want to create a form with a Command button, when we click on the command button it needs to bring the Query from a particular mdb, the path for this mdb will vary so we should be able to provide the path info, after bringing the query it needs to be run, how do I do this by just clicking couple of Command buttons?
Thanks
I have split my database and have the BE on a network server. Users were each given a copy of the MDE file to enter data from using a Form.
When I bring up my copy of the MDE and create a query or edit an existing one and give it a new name... only I can see this new query. Is this the way it's supposed to work?
What would I need to do if I wanted the query I created to be seen by everyone who has access to the database? Do I need to create the query on the MDB and then create a new MDE?
Thanks,
Michael
I am trying to create a new query and a new report in my database. The program will not let me. The "new" is grayed out. Can anyone help me? :confused:
View 14 Replies View RelatedI want to keep the user away from the tables, but want them to beable to query the tables on their own from a selection on the Main Swichboard. My question is twofold, one being is this advisable and two how would I go about that? I can't seem to find anythng on it in my searches.
View 5 Replies View RelatedDoes anyone know how to create a query object using a string that contains SQL code?
The reason I ask is that I want to be able to run an ad hoc query, but I can't use a RecordSet because there's no way (that I know of) to take the results of a recordset and create a query display out of it (meaning, make it look like a query was executed)
So what I really want to do is to create a query, and then execute it with "DoCmd.OpenQuery()" If that's possible. Any ideas?
Hi all - new here - hope to get some help :)
Situation: I have a bunch of individual databases (approx 70) - each holding one table called "Meter" with some GPS reads and additional information about the GPS point.
Am looking for solution to create one big database file holding all GPS reads.
One idea i have is linking all tables into a new database and run a create table query and then an append query.
Is there a way to somehow have the one append query run automatically on all existing linked tables (remember approx 70 tables so i really want to avoid of creating 70 append queries ;) )
To make it more complicated: data sets in the original individual databases could be modified or new data could be added. From what I know linked tables are updated but is there a way of keeping the my big, newly created (appended) database up2date automatically as well?
Hope it all makes sense?
Thanks a lot in advance
Regards from Ireland
Hi All!
I am trying to create an updateable non-calculated field in a select query. Specifically, I want to create a Yes/No data type called "Fail".
This is a multiple-user application so I can't solve the problem by using append and delete queries to modify records in a table.
This has turned into a much stickier problem than I anticipated. I would appreciate any insight or advice you have.
I have a relatively small data base.
There are a number of what I call reference tables. These are fairly static and in the main will not change.
There are a further half a dozen tables all linked together.
The system basically takes an enquiry which may lead to a repair taking place. The repair will have a number of jobs and each job may have parts replaced.
What I want to do is have an identical database which to begin with will have exact copies of all the reference tables. The remaining half a dozen tables initially will be empty but grow with time.
The user wants to be able to copy the completed repairs he selects over to the 2nd database (which is initially empty). As weeks go by he wants to select further completed jobs that have not already been copied over to the 2nd database. The 2nd database will be updated so I can't just copy over all completed repairs each time.
I had envisage him doing this by using a front end to the 2 databases and firstly selecting which of the completed jobs which haven't been copied over. This is a fairly straight forward query that will update a field on the repair table.
Once he has done this I was thinking that I could run a query that would pull all the rows from the 6 tables from database 1 and update the corresponding tables/rows on the 2nd database.
I don't actually know how to approach that. The process needs to be simple for the user. ie 1. select repairs to copy over 2. run something to copy them over.
I am using data from a sales and a finance system and essentially need to understand the difference between them and account for this "balancing" row.
My data is as follows
TBL_Sales
Month, Product_Type, Factory, Quantity, Sales, Margin
January, A, F123, 100, 1000, 10
January, A, F123, 800, 8000, 80
January, B, F123, 10, 100, 1
January, A, F124, 500, 10000, 1000
February, A, F123, 100, 1000, 10
February, A, F123, 800, 8000, 80
February, B, F123, 10, 100, 1
February, A, F124, 500, 10000, 1000
TBL_Finance
Month, Factory, Quantity, Sales, Margin
January, F123, 1000, 10000, 100
January, F124, 550, 10500, 1000
February, F123, 950, 9500, 95
February, F124, 600, 10000, 1000
I would like a query which would look at the difference between TBL_Sales and TBL_Finance and will then add rows to TBL_Sales to make them balance. In the example above I would want it to add the following (I've used C as a Product_Type to show that it's a manually entered value) -
TBL_Sales
Month, Product_Type, Factory, Quantity, Sales, Margin
January, C, F123, 90, 900, 9
January, C, F124, 50, 500, 0
February, C, F123, 40, 400, 4
February, C, F124, 100, 0, 0
I am trying to create a query that matches two other querys and finds records that are in the primary, but not the secondary query. I have tried to use the query wizard for this, but it will not work for me the way I want.
If I have A,B,C,D in one query and only have A,B,D in the other I want to find C, so that I can format an email funtion in my database using that data.
I have a table called MiscORders where all the orders are tracked. There is an ID, order number , other fields and ParentorderID fields in it. Whenever an order is modified they create a new order with ordernumber and have the previous order number as the parent. The next time it is amended or closed another order is created with the parent order attached. Here is some sample data
Order ID OrderNum Parent order id
1 MISC 2013-10
2 MISC 2013-10A MISC 2013-10
3 MISC 2013-10B MISC 2013-10
Now I am creating a query that shows the lifecycle of the order
MISC 2013-10 Issue and deadline date MISC 2013-10A issue and deadline date MISC 2013-10B issue and deadline date.I tried to create a query using self joins to the same table. SQL is attached
SELECT MISCORDER.OrderNum, MISCORDER.[Date Issued], MISCORDER.[Deadline Date], MISCORDER_1.OrderNum
FROM MISCORDER LEFT JOIN MISCORDER AS MISCORDER_1 ON MISCORDER.OrderNum = MISCORDER_1.RescindedOrderID
WHERE (((MISCORDER.OrderNum) Not Like '*A' And (MISCORDER.OrderNum) Not Like '*B' And (MISCORDER.OrderNum) Not Like '*C' And (MISCORDER.OrderNum) Not Like '*D') AND ((MISCORDER_1.OrderNum) Not Like '*B'));
How can I put a condition on the table to show only records with order num ending with A. It is not working if i use it in the where condition..
Is there a way to have Access send an alert message when a particular date is getting close? I have a database that tracks automatic step increases to pay and when the dates occur. I would like to create a pop up that could alert me when a date is getting close so that I can inform the budget person that a certain employee will be getting a bump in their pay starting on a particular date? Is that possible in 2010?
View 3 Replies View Relatedi am busy with creating a access storage database and need to calculate the number of days a vehicle is in storage, i have a [date in] field and a [date out] field. i need to calculate if [date out] is empty to use today otherwise [date out] - [date in]
View 5 Replies View RelatedI am just starting out in access. I have a table that has different vehicle models. There is a "category" field that has vehicles under boats, cars, planes, heli, etc... How do I make a query that will only show the different categories that are used? So I would just want the category field showing one of each category that exists and not multiple records with the same category.
View 7 Replies View RelatedI have a query which is used to pull data from 2 tables.
1. Only show the records that have 2 or more same Pat#, FName and LName. If it is a single record I would like to hide it.
2. Need to see the different dates amd know the difference.
Currently Query Looks like this.
Pat# FName LName Reason Status Date
00001 John Doe 1 1 11/13/2014 00002 Sally Doe 2 1 11/25/2014
00003 Bill Bates 1 1 11/04/2014
00003 Bill Bates 2 1 11/07/2014
00004 Jenny West 1 1 04/03/2014
00004 Jenny West 2 1 04/10/2014
The Signup reason number 1 represents the signup date and number 2 represents the time they left. SignupStatus number 1 represents a group.
So I am hoping my end goal it would look something like this.
Pat# FName LName Date 1 Date 2 Difference
00003 Bill Bates 11/04/2014 11/07/2014 3 Days
00004 Jenny West 04/03/2014 04/10/2014 7 days
I am trying to avoid using VBA or SQL. I do not know where to input it.
I tried to use the “Find Duplicates” query wizard but this did not work because it finds duplicates like last name first name, but it does not separate the dates.
I'm working on a contact database for my company's sales efforts. It tracks interactions with prospective customers and assigns them to employees here. My question is this: For mass contact efforts (such as mailings), is there a way to create mass entries to my "Interactions" table so that no one has to enter a mailout interaction 1000+ times?
Structure of the relevant bits of the database is as follows:
Contacts Table
Interaction Table
Contacts Table is linked with Interaction table via ContactID
Queries are run to narrow Contacts by various criteria (ex.: by the employee they're assigned to).
So if I run a query to get all the contacts assigned to me, and there are 450 of them, and I send a mailout to all of them, and I want to add an interaction to those 450 people, is there an automated way to do this?
I have some code that creates queries based on a value in a checkbox.
So, depending on that value, the queries may or may not exist.
I need to take those queries (if the exist) and create one union query.
This code creates the first query beautifully, but it won't union the second query.
Code:
Private Sub cmdSubmit_Click()
Dim blnQueryExists As Boolean
Dim cat As New ADOX.Catalog
Dim cmd As New ADODB.Command
Dim qry As ADOX.View
blnQueryExists = False
[Code] ....
So far, in my query, I have a column for Quarters and under the field of that column I have put a statement:
Quarter: IIf(Month([VisitDate]) In (4,5,6),"1",IIf(Month([VisitDate]) In (7,8,9),"2",IIf(Month([VisitDate]) In (10,11,12),"3","4")))
The statement works fine but the problem is it does not tell me which year of does that quarter belongs to. Example: whether it is Quarter 1 of 1999 or Quarter 1 of 2000. Right now it only output either 1 or 2 or 3 or 4.Therefore, is there a way where I can command to give me an output like "Quarter 1, 1999", "Quarter 2, 1999", "Quarter, 1, 2000". Basically with respect to the Year that I have in my data?
Hello all,
I have a scheduling database that I am only using one table "DateLog"
It contains these fields:
JobIDInt-PK
LotIDTxt-PK
TaskIDInt-PK - List from TaskList Table
TripIDInt-PK
TaskDateDate
ForemanIDInt - List from Foreman Table
TaskQtyInt
TaskMisc1Txt
TaskMisc2Txt
NotesMemo
Every time a trip is taken to a job site, a record is created. I would like to have a datasheet view of this table that will join all of the tasks for a particular JobID and LotID together on the same record for easy viewing.
The list of tasks are static and will not change from job to job. The only fields that needs to be seen are the JobID and LotID at the row header. The rest of the data is the TaskDate for each task.
What I have attempted so far is to create a separate query for each TaskID and then to join them by the JobID and LotID. I have gotten to the point that I can put values into the datasheet, but I get an error that the primary key cannot be null.
Am I approaching this the correct way? Is there a way I can point the hidden key fields to the fields they are joined to? Access does not seem to be smart enough to figure this out itsself and I am not sure how to tell it to create the new record using the fields that it is related to.
This is very difficult to explain, any help would be greatly appreciated. I will be glad to post further clarification if I did a bad job explaining what I am trying to do.
Thanks,
Rob
I have been running 5 reports to gather company information. I then take the data from these 5 reports and manually load them into an Excel spreadsheet to calculate a grand total. I would like to be able to run a query or report within my database to get the same information. I have attached a copy of my spreadsheet for review.
Could someone please review this spreadsheet and help me design an easier method of calculating a grand total.
I'm having trouble creating a query to give me a count of all the males with a certain ethnicity and females with a certain ethnicity. I have a table that is setup for those two categories. It has the ethnicity and gender listed with check boxes (requested by the user) and when ever I run the query with the count total, for example : Caucasian Males, it returns all Caucasians whether male or female. I've tried a few different ways to get this done, but with no success.
Here is a screenshot of my table :