Basic Sql Query
May 22, 2006
Hello all This is a pretty basic query I guess but I am having trouble solving this.. Can anyone help plsssssssss.
I have two tables
Table1
Code Serial_No Amount1 Amount2
1 ABC 100 200
1 ABC 720 220
4 ABC 150 112
9 PQR 11 25
9 ZZZ 122 44
9 ZZZ 1250 42
Table2
Code Serial_No Amount1 Amount2
1 ABC 24 20
1 PPP 72 22
4 ABC 15 12
9 PQR 11 54
9 ZZZ 22 44
9 ZZZ 150 99
and total of the amount as 844 and 440
I have write a query to select only the Code common to both the tables and add total of amounts for these codes. For eg. I want rows
Outpput
1 ABC 100 200
1 ABC 720 220
1 ABC 24 20
Thanks in advance
View Replies
ADVERTISEMENT
Sep 20, 2005
I have written a basic query that looks at the date field, department field and cost the cost field on a table. What I would like is a form that you can chose a date (19/09/2005) and a it looks at the table and calculates the total sum of money for each department for this date. Ie.
Date: 19/09/2005
Dept 1 = £123.00
Dept 2 = £1234.00
Dept 3 = £123.00
Dept 4 = £1234.00
Dept 5 = £123.00
Dept 6 = £1234.00
I can get the query to work but only 1 department at a time. Any ideas?
I have attached the DB
Thanks
D
View 6 Replies
View Related
Sep 20, 2005
OK, here's the deal. I have two data tables, one of which holds many types of customer information and has a very large number of records, including, name, address, phone, and email. The other table contains only email addresses. Each email in the second, smaller table corresponds to a record in the larger list , by virtue of both records sharing the same email address (primary key?). I need to be able to take a short list of email addresses from one table and produce all of the records from the larger table that have corresponding email addys, essentially. Can somebody shed some light on how I can structure a query to solve this dilemma? Sorry for the newb question, just getting started out using db's.. Thanks!
View 1 Replies
View Related
Aug 10, 2006
Hello, i have a small database thats purpose is to show when a book has been returned to me. I am recording the date that books are loaned out. Books are loaned for a 2 week period. I dont know if it would be easier to just manually put in the date that books should be returned (so that means just manually calculate what the date will be 2 weeks later). The reason for this is because i want a query that shows when the date has passed and how many days overdue the book is. Would this be difficult to achieve? What i was thinking is a query that calculates when the 2 week period is up and adds the unique number for book in a report.
Can anyone help?
Thanks,
Marley.
View 2 Replies
View Related
Nov 28, 2006
i have a very basic query question that i can't seem to figure out.
i have two tables that have a one-to-many relationship - one has patient ID's, and the second table has test results for each patient.
i would like to query for all positive test results - however, if a patient EVER had a positive result, i want ALL records for that patient returned.
thus, if a record in the "many" table meets a particular criterion, i want it to return all records with the same ID. does that make sense?
thanks in advance...
View 6 Replies
View Related
Jul 11, 2007
hi..
quick question...
i want to update all the records in a certain field..
to make the first letter of each word a capital
to make every letter a capital
i know how to make un update query, what to i put in the criteria if the field is called
field1
cheers
View 5 Replies
View Related
May 11, 2006
I am not sure if I understand this...
I have MainTable, on which I base MainForm. I would like to have MainForm show only the records that have a null value in CertainField. If I write NullQuery to select only those records, can I redirect MainForm to NullQuery? Well, I know I can do that... but how does MainTable get updated with new records if MainForm is based on NullQuery????
Any help is greatly appreciated.
Tom
View 4 Replies
View Related
Jun 5, 2006
Hi All,
I have two tables; "Section_Failure_Mode" and "Risk_Assessment". They are linked by a common field called section_failure_mode_id. I would like to write a query where you can delete every record in "Risk_Assessment" where the checkbox (called Selected) is not selected (i.e. equal to 0) in the table "Section_Failure_Mode". However, I do not want any records to be deleted from "Section_Failure_Mode", only from "Risk_Assessment".
I have put a link to the screen cap of the query I have tried below. Is this the correct way to do it?
13797
Best Regards,
Aaron
View 1 Replies
View Related
Sep 18, 2006
I have an access Query(named newSerial) :
SELECT TOP 1 (Productbase.Serialnumber+1) AS Expr1 FROM ProductBase
ORDER BY (Productbase.Serialnumber+1) DESC;
I want to access this new value. Its not a key since an autonumber may be any number. When I create a new product. How do I get this value inside a form?
It works fine when clicking on it. But if I use an unbound box I get 1 as result and not like 76067 which it should be. If I click the query in access it works fine and show 76067.
I tried:
outbox=Expr1
' gives null
'or
outbox=[newserial].[Expr1]
' which gives "Access cant find the field "|" refered to you in your expression"
What should I write? Whats the correct expression?
View 7 Replies
View Related
Sep 10, 2004
Is it possible to use a value on an Access driven form as a parameter in a pass through query?
View 4 Replies
View Related
Oct 20, 2004
Hello,
I am hoping someone might be able to help me out. I am trying to create a delete query in VB for access.
I have managed to get it to run the delete query, but I would like to program in the responses to the following questions:
"You are about to run a delete query that will modify data in your table. Are you sure.... Yes"
"You are about to delete X row(s) from the specified table. Yes"
Here is what I have so far:
Private Sub Command32_Click()
DoCmd.OpenQuery "DELETEQUERY", acViewNormal, acEdit
End Sub
Does anyone know how to do this???
Thanks,
Jason
View 2 Replies
View Related
Oct 18, 2006
hello.. i have 2 tables
CUSTOMER TABLE
customerid
customername
customerphone
BOOKING TABLE
bookingid
bookingdate
customerid
At the moment i have 2 forms.. one customer form based on the customer table and one booking form based on the booking table.
at the moment, my user enters a customer record using the customer form.. they then save and close the customer form..
they then open the booking form.. and in the customer id field they enter the customers id.. this way, they successfully register that customer to that booking..
what i want to do is allow the user to enter the customer details and the booking details in one form..
i assume i would have to create a third form based on a query..
if i include all the fields from both tables in this query.. and then make a form based on this query, how can i make the following 2 things happen..?
1. when the third form is opened and customer details are entered... a new customer record will be added to the customer table (with a new customerid)..
2. this id will then need to be auto placed into the foreign key customer id field in the booking table.
do you get me?
View 3 Replies
View Related
May 10, 2013
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)
View 1 Replies
View Related
Apr 3, 2014
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.
View 3 Replies
View Related
Feb 21, 2007
Please can you guys give me some basic help, im pulling my hair out over this one!
Attached is a test database which im writing at work, what I would like to happen is that when a new machine ID is selected from the drop down box (combo box) all the relevant fields update with all the information in them.
Please could somebody download my test database have a quick look, see if they can sort it for me then upload it back and explain how they did it.
Thanks in advance
Andrew.
View 6 Replies
View Related
Oct 3, 2005
I will try and be as concise as I can...
I am trying to create a database for our organization that will keep track of our "Gift Fund". I have a table with all of our personnel that includes all of their basic info with a customer ID field as the PK. I then have a table that is for all of the transactions. Deposits, withdrawls, description, date, customer ID to relate the two tables and transaction ID as the PK.
So we have a table with our personnel and a table to record transactions. Transactions occur anytime we put money in or take money out as well as when one of our personnel becomes a member of this fund by paying their "dues".
I have created a form to update our personnel table. It works fine. I also have a form to enter basic transactions. It also works fine. My problem lies where I try to make a form that will add a member to this fund (which in the personnel table is a yes/no checkbox) at the same time entering this as a transaction. Ideally the form would have the user pick personnel from a drop down list, click the yes/no checkbox making them a member and then entering their "dues" as a transaction.
Ultimately I would like to be able present this info in several dif reports showing all the silly statistics that boss' love to see.
Any help would be appreciated and if I have left out any key info, let me knw and I will be more specific.
View 2 Replies
View Related
Mar 1, 2006
Hi there,
Having read many posts here regarding normalization, table designs with a view to avoiding duplication of data, avoiding storing of calculated contols into a field in a table (duplication as well as danger of keeping data updated, etc) - I would like to clarify something...
I've designed 2 databases, one sticking to the rules and one breaking them...
I hear the 'don't store' - calculate on demand principle - and this works well in the case of closing a new record where you 'store' a value by passing a subtotal from a subform to a field in the main form. All that passing of values works but there are time delays. Look at the time delay of summing a range of values in a form footer...bout half a second in general...So in this case, storing values fails...(incidentally is there an 'event' for this time delay a calculated control takes to show it's value in a form? - OnErrorCausedByDelayOfCalculatedControlToDisplayIt 'sValue?? Oops ignore the apostrophe ;-).....).
On the other hand you may want to work through many thousands of records, summarising all your data into reports, charts, etc...It just seems that if you stored each records 'total' (say an invoice total) it would be quicker for to work through thousands of 'ready stored numbers' rather than thousands of 'calculations to get those numbers'...or maybe I'm missing something!?
Sorry for all the fuss, but I'm self taught and I wish I wasn't LOL. I shoulda definitely done college instead of getting hooked on databases in my 30's)
And I'm also sorry if this question has been asked before, even under different wording...
Best regards
dazza61
View 1 Replies
View Related
Mar 22, 2006
A family is to enter basic income and expenses data comprising of date, description, catagory and price. The family don't work on a budget that is lost at the end of the month if it is not used, but rather it accumulates in each allocated catagory. example: Money not spent on a 'holiday' this month will build up each month in the 'holiday' catagory until it is spent.
They need to enter data. Query, add, modify, and delete records. Also, to print simple reports.
1. Is this a big project for a novice?
2. What exactly is required to be done? i.e What do you suggest I need?
3. Would it be better to buy SimplyAccounting Basic if it did the job?
View 1 Replies
View Related
Jul 23, 2007
I have forgotten everything about Access!
I have two tables: A & B, and I want to join the two by a common field, and only add one field from B to the A table (based on the common field).
How do I accomplish this?
Thanks!
:confused:
View 11 Replies
View Related
Dec 4, 2007
Hey. This is a really basic question but i've looked through a few tutorials and couldn't find an answer
If i have this statement
SELECT * FROM timesheet
WHERE Weekending='07/12/07' FNAME='James.90';
How do i filter it with 2 parameters
such as the Weekending and the FNAME. When i run it, it gives me the following error
Syntax Error (missing operator) in query expression Weekending="07/12/07" FNAME="James.90".
Thanks
View 4 Replies
View Related
Mar 28, 2008
Hi,
I'm trying to understand queries a little better -
If I have a query linked to a Report does the query run first - then open the report?
It's just that If I make a new entry to a table the query seems not to be showing the new entry.
Secondly there are different types of query - what are they for?
EG
Updat
Append etc
Cheers
Andy
View 4 Replies
View Related
Feb 16, 2005
Hi,
Many thanks to those who helped with my last couple of problems.
I've allmost completed my project now and am debugging.
I've found that in order to close one of my forms, which has combo boxes on it, I need to have no values selected in those combo boxes. Could someone give me the visual basic script (or any alternative meathod) for resetting the combo boxes to their default values; i.e. nothing selected in them!
Many thanks,
Robin :o
View 3 Replies
View Related
Jul 6, 2005
I have a table with eight fields. I want to do a chart where I the piles are "tighten" togther two and two. I would like the chart to look something like this (Example):
|_______________________________
|___ _ __________________________
|___|x|______ _ ____ _ _____ _ ____
|___|x|_ ___ _| |___|x|_ ____|x|___
|___|x| |___|x| |___|x| |____|x|____
|___|x| |___|x| |___|x| |__ _|x|____
|___|x| |___|x| |___|x| |__| |x|____
View 5 Replies
View Related
Oct 31, 2005
Ok I’m coding a form, heres what I have done so far
Me.FN LN =DLookUp("[First Name] & ' ' & [Last Name]","tblStaffID", "Staff ID=" & Me.Staff ID")
I have a txt box to imput the Staff ID number in to and I want it to return the first and last name in to another txt box
Am I close??? What am I doing wrong here??
The table this is coming off is just three fields “Staff ID” “Last Name” “First Name”
Can anyone help??
View 9 Replies
View Related
Jan 12, 2006
Hi
Im an absolute noob when it comes to Access so please bear with me
For work I want to make a database to keep track of ordering, so thus far i have made 2 tables: suppliers and products
in my form to add products, i want it to display a drop down with the supplierName in it, but store the supplierID in the products table.
I figure this should be pretty simple, and could easily do it if this were php....
Can anyone help?
View 4 Replies
View Related
May 19, 2006
Hello,
Help appreciated,
trying to create a form so that when I type in a value it will bring back held in a table it will display two fields held in the same row. i.e.
Table 1
no
first name
surname
I want to type the no field and it displays the appropriate first name & surname fields for the row.
View 1 Replies
View Related
