Need Help Creating A Simple Query. Easy For Any Of You. Pls Help
Jun 14, 2004
Hi,
I don't know how to create a simple query that will compare 2 tables and create a new one after that.
Each table has only one field.
Example:
Table 1 has records A, B, C, D and E
Table 2 has records A and C
I want new table to be like table 1 minus table 2. Like cleaning up table 1.
New table would have records B, D and E
I think it's very simple but I can figure how to do it.
Could you pls give me advice?
Thanks a lot,
skuba
View Replies
ADVERTISEMENT
Jan 3, 2005
I need to create a query that will output the number of times doors have been used during time bands of a day.
I have no problem getting the info I want from the doors that have been used (in other words where there is a record in the field) however, how can I ammend the attached statement so that it reports no records as a "Zero"? in the same format always ie,
North Entrance, 2
South Entrance, 4
East Exit, 0
West Entrance, 3
I need the data in this format so that I can export it to excel.
Here is the SQL view of the query I am using
SELECT [Staff Entrance].[Door Location], Count([Staff Entrance].Time) AS CountOfTime
FROM [Staff Entrance]
WHERE ((([Staff Entrance].Time)>#12/30/1899 2:30:0# And ([Staff Entrance].Time)<#12/30/1899 23:0:0#))
GROUP BY [Staff Entrance].[Door Location];
The sample table is attached
Thanks in advance
View 5 Replies
View Related
Feb 18, 2008
Hi there,
I'm creating a database for my computer repair business and it's going to store details on my customers, their computers, and any repairs I carry out on them.
I've created some tables, but I'm unable to create the relationships I require between the tables. Here's what I have:
http://img352.imageshack.us/img352/4724/helpbk3.jpg
I want to create a one-to-many relationship between the tables left to right, excluding the Engineer table, which I will talk about in a moment.
The idea is - One customer has "many" computers, one computer has "many" repairs, one repair has "many" problems. Each repair has just ONE engineer assigned to it, although the engineer will be assigned to many repairs.
I can link the Customer and Computer tables, via the CustomerID, but when I try to connect the other tables, I get the "Relationship Type: Indeterminate" error.
Can anyone help me with what fields I need in each table, and what relationships I need to achieve what I need?
Many thanks :)
Paul
View 2 Replies
View Related
May 24, 2007
hiya every1, (access 2k3)
quick easy question, as im a noob when it comes to coding/vb.
Within our company database we have the usual timesheet table. With the table I have create a query (called "dailytimeforkeith") that gives me the sum of time for an individual person for one day (in fact its the previous day). What I have done then is created a form (called "keithyesterdaytime") that shows this query entry (called "sumoftimespent").
Each user has a different database screen logon and forms that are present when they log in. So what I then done is on one of these forms the load up, i attached this:
Private Sub Employee_Enter()
Me.Employee = User.FirstName
If Me.Employee = "Keith" Then
Dim stdocname As String
stdocname = "keithyesterdaytime"
DoCmd.OpenForm stdocname, , , acAdd
............
then on "keithyesterdaytime" opening the following code runs
------------------------------------------------------------
Private Sub Form_Open(Cancel As Integer)
Dim internal1 As Integer
internal1 = Forms!keithyesterdaytime.SumOfTimeSpent
If internal1 < 6 Then
DoCmd.Close
MsgBox " Less than 6 hours have been entered " & Chr(13) & " into your timesheet for yesterday. ", vbCritical, Title1
Else
DoCmd.Close
End If
End Sub
-------------------------------------------------------
Whenever i logon with the user keith, i get the following error 'runtime error 2427: You entered an expression that has no value.
If I open the form "keithyesterdaytime" on its own, the code works and i dont get an error.
What have i done wrong, and be gently. HEHEHE. Also is there any easy way i can do this instead of having to create the the form "keithyesterdaytime"
View 2 Replies
View Related
May 17, 2007
AFternoon,
I have never used Access before and i have been asked to create a report that displays the cartridge name and the quantity. This part was easy. But, the problem i am having is that i have a field in the database called "DoNotOrder". If the cartidge has the box ticked in the field "DoNotOrder", I do not want it to be displyed in the report. I was wondering if anybody could give me a clue into writing a statement to do such a thing.
ANy help is much appreciated.
Thanks for your time
Ollie
View 1 Replies
View Related
Dec 17, 2007
I have basically a list containing 400 names and addresses. For each address, an event can occur. If and when it does, I add 3 fields to hold the date, type, and result of the event.
Simple enough, but I am at a complete loss as to how to make a simple rdb with table 1 containing 400 rows of address data and table 2 containing event data. Hopefully somebody can help me.
My table in Access 2000 in its simplest form consists of the following fields:
ID (primary key)
Address (actually several fields)
Date1
Event1
Result1
Date2
Event2
Result2
|
|
DateN
EventN
ResultN
View 10 Replies
View Related
May 19, 2012
I need to create buyer and seller invoices within my access 2003 databases, my sister in law runs an antiques auction house and I'm working on a database to capture all their information.
I've created a 2 queries (a buyer invoice and seller invoice), show all unsettled items with the relevant item information and fee's.
However, I would like it to automatically allocate an invoice number, and store the information back to a table (seller and buyer ID, total number of items on the invoice, total fee etc)
I'd like store the invoices as well, so within the customer record form, I can include a box showing a list of the buyer invoices on one side and seller invoices in another (not all customers are buyers and sellers).
Once I've issued an invoice, can I automate it to show that item as then as invoiced?
Can the invoices be editable at all?
When they come in and settle their account, I also need to feed this information back in as well.
Can i arrange an invoice for seller's particularly, to show all sold items, and the fee's associated with them
Returned items as well as any associated fee's with them?
I have been looking at the Northwind example.
I see how they have an orders details table and orders table, I could replicate this.
I could have an Buyers Invoice table and Buyers Invoice Details table, but how do I generate an invoice, pull the next Invoice number from my (currently blank) invoice table, and pull in the outstanding items for that buyer for that specific auction date (I have a query), and then populate back information from the invoice?
I like the fact that their order form is editable, do any changes go back to the query, that then populate the invoice when you click print invoice?
I need to be able to (at the end of the auction) pull all items from my item log, for that buyer, and print them an invoice, automatically saving all the invoice details back into my database.
I need be able to automatically flag the items in my items table, as invoiced and pull the invoice number in?
I can see how all the Northwind tables, queries, and forms relate to each other and subforms, but I'm not sure how to actually create an invoice, get invoice number, merge with my query, and then feedback in.
View 1 Replies
View Related
Oct 16, 2007
Hello
I'm a beginner when it comes to Access. Right now I'm trying to create a movie database and need help.
I want to be able to search for a title that contains a specific word. For example, I have a movie called "the cube", right now i can only find the movie by typing the full or a part of the correct sequence of the title. For example I can find the movie when typing "the" or "the cube" but not when typing only "cube". This is my problem.
Right now my query looks like this:
SELECT Movies.Title
FROM Movies
WHERE Movies.Title Like Forms!frm_search_title!search & "*";
Please help.
View 2 Replies
View Related
Aug 10, 2005
I have used Excel for many years and used the if formula. I have just started using access and need a little help. I have a table the has 20 fields. I am only concerned with about 8 of these fields for a report. I have a field named completed in this field I put a "x" if the job is complete. I would like to create a report that shows me all active jobs. Everything that does not have an "x" in complete field. Can somebody either direct my to the forums I should be in or possible give me the correct coding. Thanks for your help in advance.
View 8 Replies
View Related
Aug 29, 2006
G'day all,
I'm a novice to Access. I'm trying to setup an adhoc query, whereby a user can select all the records for a particular month.
I've tried using ([month]) in the criteria row but I get an error message??? :confused: :confused:
View 4 Replies
View Related
Dec 28, 2006
HI,
I have the next 3 tables,
*************************************************
Table USER: id_user (primary key)
name
Rec: id_user, name
------------------
rec: 1, A
rec: 2, B
rec: 3, C
*************************************************
Table AP : id_ap (primary key)
nr_ap (primary key)
id_user
id_sort
Rec: id_ap, nr_ap, id_user, id_sort
-------------------------------------
rec: 1, 2, 1, 1
rec: 2, 5, 2, 1
rec: 3, 1, 3, 1
rec: 4, 3, 1, 2
rec: 5, 4, 2, 2
rec: 6, 3, 3, 2
NOTE:
SELECT sum(nr_ap) AS Total FROM AP
with id_sort = 1 -> SUM(nr_ap) = 2+5+1 = 8
with id_sort = 2 -> SUM(nr_ap) = 3+4+3 = 10
*************************************************
Table SORT: id_sort (primary key)
value
Rec: id_sort, value
------------------
rec: 1, 80
rec: 2, 200
**********************************************
I need do create a QUERY that for all records in SORT table, it will
show only for user 'A', the rescords as in the result shown below.
NOTE: xxxx = (valueSUM(nr_ap))*nr_ap
RESULT EXPECTED
------------------
id_sort, name, ap, value, xxxx
1 A 2 80 20 (808)*2 = 20
2 A 3 200 60 (20010)*3 = 60
Regards,
Elio
View 3 Replies
View Related
Feb 21, 2008
I've posted this on another forum, but I'm not getting any help on it. Let me know if I need to explain it in more detail.
I have one table containing a list of addresses:
Address
1301 Main St.
I have another table containing names :
Name 1
John
I have yet another table containing names:
Name 2
Chris
The primary key for the address table matches up with the primary keys in the name tables. I need to write a query that will pair the addresses in the address table with the names from each of the name tables. It should look like this:
Address..................Name
1301 Main St. .........John
1301 Main St. .........Chris
Can anyone help? Thanks.
View 2 Replies
View Related
Apr 20, 2005
I have a table with 8000 records. Now i have two fields (last name) and(ID). Now i want to see all the people that have different ids and that only???
ex
smith 10001
smith 10002
smith 10003
extc
example please
View 1 Replies
View Related
Jun 13, 2007
Is there a relatively easy way to output the results of a query to an XML file?
I have the file writer set up more or less:
Code: Dim intFn As Integer Dim strFilePath As String Dim strOutBuf As String strFilePath = "c: emp est.xml" intFn = FreeFile Open strFilePath For Binary Access Write As #intFn strOutBuf = "<?xml version=" & Chr(34) & "1.0" & Chr(34) & " standalone=" _ & Chr(34) & "yes" & Chr(34) & "?>" & vbCrLf strOutBuf = strOutBuf & "<file>" & vbCrLf 'DO FOR EACH LOOP HERE strOutBuf = strOutBuf & "</file>" Put #intFn, , strOutBuf Close #intFn
If this were a C or JAVA application, I would just write a "for each" statement similar to as follows:
Code:For Each (Row in Query){ strOutBuf = strOutBuf & "<entry>" & vbCrLf For Each (Field in Row){ strOutBuf = strOutBuf & "<" & (Column Name) & ">" & (Value) _ & "</" & (Column Name) + ">" & vbCrLf } strOutBuf = strOutBuf & "</entry>" & vbCrLf}
What commands would I use in Access to get each separate row in a query? And then to get each field in that row? And how about the column names for the XML tags?
Thank you for your help.
View 4 Replies
View Related
Dec 27, 2005
Hi all
I'm currently working through developing my first database.
Basically, what I would like is for a query to run from a command button.
The query should look at a field in a table (a yes/no field) and report back the ones that have been filled in.
I know how to run the query from the access 'Query' window, BUT, I would like this query to run from a click of a command button on a form.
I've looked it up in a book, which explains about SQL, and didn't really understand it. I've also searched on here, but people talk about Macro's, and I don't know anything about them.
Can anyone help?
Cheers
View 2 Replies
View Related
May 11, 2005
Hi there,
I have a really simple question.
I have a field in a table that contains comments (text format). I want to run a query where I can get only the records that contain the word "high" in the comments. There may be some typos so I would like records returned that also contain something like "high". High is not the only word in the comments.
Does this make sense?
Thanks,
Row
View 4 Replies
View Related
Jul 26, 2005
Hi guys, wondering if you could help me, I can't seem to get this right..
I have two tables in question:
Items: StyleTypes:
------ ------------
ItemID ItemType
ItemType StyleType
Style
Lots of Attributes
ItemType is a list of items (Table, Desk, Bookshelf, Chair)
Style is a type of style for the item (Circular table, Radial Desk)
I want to create the Items.Style field based upon a query of the StyleTypes. So if a user types "Desk" in Items.ItemType then the Items.Style field will be limited to only those Styles which match that selected item within the StyleTypes table.
Think thats clear enough, but my attempts have all failed, hope you guys can give me a push in the right direction, thank you!
View 4 Replies
View Related
Apr 5, 2006
Hi all,
I have very little knowledge of databases and even less about writing queries.
The problem:
I have a database that lists cities (such as Paris, London, New York etc) as rows and employment status (Full-time, Part-time, Unemployed etc) as columns.
I need to go through each city and record which employment status is greatest (for each city) and present it in a new column. I can do this manually but I’m sure a query can be created to automate the process.
Hope that all made sense. Can anyone suggest ideas on where to start?
Many thanks,
Carl
View 1 Replies
View Related
May 6, 2006
hi
have just designed a rather simple music database for my dad to use at home as he has large collection.
have a table with the heading"artist" and another one with "album" ...my problem is that on the table there maybe 1 or more instances with the same artist but with different albums...when i run a simple query for ..say artists starting with the letter"a" i may get about 10 cases of the same artist appearing in my query when all i want is one...(depending on if i may put in say one artist like abba having 10 albums etc)...i am entering each artist with the album each time so i may enter the artist in say 10 times....depending on how many albums etc
if you understand all that...i would be grateful if someone could help me with a simple query
alan
View 7 Replies
View Related
Jul 4, 2006
I have a query that returns the uncompleted jobs from a single table ( its a calls database)
all i need is query that returns the amount of uncompleted jobs onto the switchboard I.E whatshername has X amount of uncompleted jobs , i have done a search and most of the solutions are very complicated for what i want to do .....anyone any ideas ?? pls
View 2 Replies
View Related
Jul 6, 2006
Thanks in advance to anyone who helps.
SQL and Access are both new to me. I have purchased some books (which are still in the mail), and have gone through this forum (which seems to be a little more advanced for me at this moment).
I have a database that contains Business_Unit (Store Numbers), Post_Date (Last Date of Inventory), and other misc. stuff. Anyways, My goal is to pull the latest date of inventory for each individual store before the last ninety days. What I have so far:
SELECT DISTINCT informix_shrink_head.business_unit, informix_shrink_head.counting_event_id, informix_shrink_head.post_date, informix_shrink_head.delta_cost, informix_shrink_head.delta_count, informix_shrink_head.count_reason_cd, pcw_loc_master.closed
FROM informix_shrink_head, pcw_loc_master
WHERE (((informix_shrink_head.post_date)<=Date()-90) AND ((pcw_loc_master.closed) Is Null))
ORDER BY informix_shrink_head.business_unit;
However, I get the same store multiple times and for multiple post dates.
I do not even know if this can be done in one simple swoop, or if I need to create another table and re-query that? Any assistance or comments would be much appreciated.
Thanks,
A Gator
View 1 Replies
View Related
Sep 11, 2006
Hi guys. I have been away a while and boy am I rusty.
I would like to show how many available licenses there are. Here is what I have:
Software (Office 2003)
license purchased (8)
license used (2)
I added an expression along the lines of:
Available: Sum([tblSoftware]![Number of licences purchased]-[tblPC-LicenceRelation]![Number of licences])
This gave 14 and not the desired result of 6!!!
Simple I am sure!! :o
Phil.
View 4 Replies
View Related
Sep 26, 2006
Hi Guys,
I have a database that collects information on products when engineers return them, what I need to do is when they have submitted the form, email it to 3 people so that the paperwork can be raised.
I understand that I will need to create a query to display just the last record and then email it.
So the idea is when a record is created and the engineer clicks submit the last record then gets emailed to the three people.
Please be gental with me im still quite new to all this.
How do i create a query that just displays the last record?:confused:
Thanks in advance.
Rich
View 1 Replies
View Related
Nov 21, 2006
I built an update query that adds two strings of text together with a _ inbetween.
eg
field1 = Hello
Field2 = Jamie
after update= Hello_Jamie
This works fine but when i attempt to run the code in SQL on an after update instance i can't get it to compile
The SQL straight from the working Update query is:
UPDATE tblPID SET tblPID.PIDPIDNoRev = (tblPID.PIDno)+"_"+(tblPID.PIDRev);
I have tried quite a few different ways and believe it to be a probelm with the speech marks or underscore
DoCmd.RunSQL "UPDATE [tblPID] SET [tblPID].PIDPIDNoRev = (([tblPID].PIDno)+"_"+([tblPID].PIDRev));"
anyone got any ideas? i'm sure it's pretty simple
Thanks, Jamie
View 2 Replies
View Related
Dec 29, 2006
HI,
I am sending ZIP file in attach with files in it. I would like some help to change the 'Q_test' query so that I can get the result shown in the excel file.
Regards,
Elio
View 1 Replies
View Related
Jan 4, 2007
Hi,
I think i am being a bit thick, all I want to do is create a report of projects without products....
I have a table of Projects (customer name, details etc...)
I have a table of Products (product name, config, details etc....)
Every project will eventually have one or more products allocated to it...
How can i query the database for:
'select all projects created without any assigned products... yet'
I'm having a post xmas block!!!
Thanks
View 1 Replies
View Related