Generating A Query Using 4 Tables And Totalling One Of The Columns :S
Feb 1, 2006
Hi guys, I am trying to create a query collecting data from 4 tables as well
as calculating totals delivered but it's hurting my head trying to work out
all the joins can anyone help please?
The 4 tables are as follows:
Orders
=====
OrderNumber
CustomerName
CustomersRef
OrderDetails
=========
Order_Items_ID
OrderNumber
ItemNumber
Qty
ItemType
Description
Deliveries
=======
DeliveryNoteID
OrderNumber
Date
DeliveryDetails
==========
DeliveryDetailID
DeliveryNoteID
ItemNumber
QtyDelivered
The relationships are pretty straight forward, OrderNumber on Orders and
OrderDetails are linked as are DeliveryNoteID on Deliveries and Delivery
Details.
I want the query to list every single item for all orders as well calculating
how the quantity remaining to be delivered based on deliveries so far and Qty ordered.
So I want to create a report that pretty much just displays:
Orders.OrderNumber - Orders.CustomerName - Orders.Customer_Ref -
OrdersDetails.ItemNumber - OrdersDetails.Qty - OrdersDetails.ItemType -
OrdersDetails.ItemDescription - Deliveries.DeliveryNoteID - Deliveries.Date -
DeliveryDetails.TotalQuantityDelivered
Can this be done as one query or do I need to join two queries together?
It's hurting my head :(
View Replies
ADVERTISEMENT
Mar 22, 2007
I'm affraid my confusing topic title is an indicator of how confused I am by this. I can't even understand the variables well enough to fully utilize Access Help or the Search function here...
What I have is a database hat has column headers that look something like this:
Customer_Name, Order_Date, Qty_Ord, Unit_Price, Total_Price
What I'm trying to get is a query output that will have
Customer_Name, Total Orders (in Dollars) for January, Total Orders (in Dollars) for February, Total Orders (in Dollars) for March, etc.
I've been able to set it up to SUM for one month, but not multiples.
I know I'm totally lame (for proof read any of my previous posts) but you guys totally bailed me out the other time I asked a lame question.
Thanks in advance!
View 3 Replies
View Related
Dec 13, 2006
Ok, I have a problem. I have two tables with different data pulled from two different areas, but that have the same type of information in them. I need them merged into one query or table so it can be exported into excel.
The problem is that one table has data only once (has a primary key, then things by building and grade). The other table has it's primary key, but then could have more then one building and grade combination (i.e. building 1050 grade 8 membership of 10 more then once becuase off another factor).
I need to merge this stuff by query of some kind into another query or table that totals all things with the same primary key, building and grade. So there is only one line for xxx-xxx building 1050 grade 8 with the grand total of membership for all.
How do I do that?
View 1 Replies
View Related
Oct 5, 2004
i have a query that uses data from a series of tables, the trouble i have is that if one of the tables is null i don't get any data returned.
current query:
Code:Dtime: [Mktbl_otherDTime]![Other]+[Mktbl_UnacDTime]![Total]+[mk_tblBreakdowns]![Breakdowns]
example:
if i have data in:-
[Mktbl_otherDTime]![Other]
[Mktbl_UnacDTime]![Total]
but no value in:-
[mk_tblBreakdowns]![Breakdowns]
i need the total of tables with data so effectivly the third table will be classed as zero
how should i do this please
Andy
View 1 Replies
View Related
Aug 23, 2005
Hi. I have a simple access table that contains details of our customers, and what products they have bought from us. Each row represents a product bought, and contains the customer details & the value of that product.
What I wish to do is create a report that lists these purchases, grouped by the client. That much I can do. However I also wish to sort the report by the total spend of the customer.
What I think I need to do is create a query that totals the "income" column for each customer, and then sort the report on that new figure.
All I cant work out, is how ?. Could someone please let me know how best to do this - small words please - my ambition exceeds my ability !.
Thanks all.
View 1 Replies
View Related
Oct 31, 2006
Greetings,
I am working with a database where I query a table and get a set of data. it is a phone log database and I am trying to find the total number of calls that a certain ext. makes during a certain time. I have it now where it will give me a complete list of the calls made for the dates and the times, but it gives one for each call, I am just looking for a total for each ext not the detailed that it is giving me. any help would be much appreciated thank you
View 5 Replies
View Related
Jun 3, 2014
I am trying to create a database to manage some project related functions. the main thing I want it to do is manage the resource allocation and costs related to fixed price projects and ultimately be able to produce reports that show the Bid vs Forecast vs Actual in relation to someones time and hence cost.in building the first table which I want to store the initial Bid data. I have a need to have fields automatically generate based on the value of two other fields. this is used to identify how long the project will run for.
for example:
prject start date of 1/1/2014
project end date of 31/12/2014
Auto Generate 12 fields in which to put the monthly resource usage for each resource.
View 3 Replies
View Related
Dec 9, 2013
I'm new to Access. We have a database that was created years ago and has been working fine. Now suddendly we get the following error message on a query.
"The number of columns in the two selected tables or queries of a union query do not match"
This is the code
SELECT [TimeSheets All].[Job Number], [TimeSheets All].Date, [TimeSheets All].Details, [TimeSheets All].[Start Time], [TimeSheets All].[End Time], [TimeSheets All].[Unbillable hours], [TimeSheets All].Who, *
FROM [TimeSheets All]
WHERE ((([TimeSheets All].[Job Number]) Like [Forms]![Search]![Job Number]))
ORDER BY [TimeSheets All].[Job Number], [TimeSheets All].Date;
View 3 Replies
View Related
Mar 12, 2013
i have a table on my database from which i generate a monthly report by running a query. when i run the query (date from to date to) i have no problem. it shows me all the relevant records. when i want to generate a report, i input the start date and the closing date for the report. it then gives me an error (reserved error (-1524); there is no message for this error.) i have gone into the table and basically isolated all the corrupted entries. there are 359
what i need to know is how to repair these entries without losing the information. im new to access and this is a simple(kinda) database which i set up to keep track of what the staff in my department are doing on a daily basis.
im running access 2007.
View 7 Replies
View Related
Aug 8, 2013
I was able to use the UNION ALL qry. But, when I have another file (like original2) that does NOT have all the columns listed in the UNION ALL qry, I get a Parameter value box asking for the missing columns when I run the qry.
Example:
original1IDDateGroupChristianJohnnySteve 18/5/2013A1528/5/2013B338/5/2013C2348/5/2013D2358/5/2013E5
original 2IDDateGroupChristianJohnny18/6/2013A212528/6/2013B2338/6/2013C2248/6/2013D22
The UNION ALL qry includes all the possible resources ( includes all the possible column fields Christan, Johnny, and Steve).
When I run the UNION ALL qry with the original2 file, An "Enter Parameter Value" box is displayed with the mssing column name "Steve".
Is there a way to Map the original2 table into a working table with all the columns, or use VBA code to construct the UNION ALL qry to only include the existing columns? My data has variable columns and I'm trying to avoid the parameter popups.
View 2 Replies
View Related
Feb 9, 2006
I want to know how to generate a result from a query in a control of a form? For exampel I have made a SQL count function in a query and I want to use this result i a control of a form.
View 1 Replies
View Related
Jun 3, 2014
If I use this query I get the results what I want
SELECT tblMainTWTTPSheet.txtRootCause, Count(tblMainTWTTPSheet.txtRootCause) AS CountOftxtRootCause
FROM tblMainTWTTPSheet
GROUP BY tblMainTWTTPSheet.txtRootCause
HAVING (((tblMainTWTTPSheet.txtRootCause) Is Not Null) AND ((Count(tblMainTWTTPSheet.txtRootCause)) Is Not Null))
ORDER BY Count(tblMainTWTTPSheet.txtRootCause) DESC;
But What I am trying to do is on a form select dates and generate data in this query. which doesnot happen even if I use the following query.
SELECT tblMainTWTTPSheet.txtRootCause, Count(tblMainTWTTPSheet.txtRootCause) AS CountOftxtRootCause
FROM tblMainTWTTPSheet
WHERE (((tblMainTWTTPSheet.date) Between [Forms]![frmStratificationOfRootCauses]![startDate] And [Forms]![frmStratificationOfRootCauses]![endDate]))
GROUP BY tblMainTWTTPSheet.txtRootCause
HAVING (((tblMainTWTTPSheet.txtRootCause) Is Not Null) AND ((Count(tblMainTWTTPSheet.txtRootCause)) Is Not Null));
View 4 Replies
View Related
Jan 3, 2008
Here's my problem. I need to generate a report that says how much of each individual product was produced and as well as the total produced for a specified category in a time period. Something like the following:
05 Catagory A: 02 Product AA, 01 Product AB, 02 Product AC
10 Category B: 07 Product BA, 03 Product BB
04 Category C: 01 Product CA, 01 Product CB, 01 Product CC, 01 Product CD
etc...
I currently have a query that queries a database and pulls out all products that were produced in a specified period and the categories they belong to and dump them into a local access table. Now what I need to do is search through the query results and count up how many of each product were produced (02 AA, 01 AB, etc...) and the totals for each category. The number of categories is pretty limited (6), but there are hundreds of product codes, so I need a way to do this without having to type in each induvidual product code as the requirement by which the query searches. Also, the product codes that get returned are different every day.
I was thinking something along the lines of take the product code of the first row and check for any others in the results that match and write that into another table. Then move onto row 2 and use its product code as a search parameter and search through the query results for any matches. Then continue that until the end of the query results. Can I do that? Is there a better way to achieve what I need?
View 7 Replies
View Related
Jul 3, 2014
I am trying to run simple query make a chart in report through this query but its not generating chart?
SELECT [Unsafe Act Unsafe Condition].[Inspection Audit], Count([Unsafe Act Unsafe Condition].[Inspection Audit]) AS [CountOfInspection Audit]
FROM [Unsafe Act Unsafe Condition]
WHERE ((([Unsafe Act Unsafe Condition].Date) Between [Forms]![frmSafetyReportOut]![startDate] And [Forms]![frmSafetyReportOut]![endDate])) OR ((([Unsafe Act Unsafe Condition].Department)=[Forms]![frmSafetyReportOut]![cboDepartment]))
GROUP BY [Unsafe Act Unsafe Condition].[Inspection Audit]
HAVING ((([Unsafe Act Unsafe Condition].[Inspection Audit]) Is Not Null) AND ((Count([Unsafe Act Unsafe Condition].[Inspection Audit])) Is Not Null));
View 1 Replies
View Related
Mar 2, 2005
Hello All,
How can I create reports in Access based on dynamic queries? I did a lot of search on this but couldn't find anything reasonable.
Any help to get me started will be extremely appreciated.
Thanks
View 1 Replies
View Related
May 17, 2015
I've come across a problem that doesn't make sense to me. I have a form with a combo box on it. Each time the combo box is used or the form is moved to another record it triggers an event to update the text boxes.
I get the information for the text boxes from a query. Everything was working smoothly until I decided to add a text box to the form to be filled in. My thought was I add the column from the query to the code and it will update. No dice. It doesn't recognize any information in the query. But when I run the query as a standalone it sees the text. When i open the table that holds the text, it is still there.
Code:
Private Sub cboDoctor_AfterUpdate()
'update doctor fields on the update of the combo box
If Len(Me.cboDoctor) > 0 Then
Call DoctorName_Change
[code]...
When i step through the code it shows values for each of the columns except for column 11. It reports as 'null.' Things I've tried:
-Updating the combo box by reselecting the value for the record.
-Restarting DB.
-Changing from one record in the combo box back to the original record.
Is there a limit of columns that can be used? Column 10 works just fine...
View 3 Replies
View Related
Jun 23, 2006
(This is a modified repost - which hopefully makes sense) I am using Access2003. I am trying to set up a fast method of creating a union query. I have a jobs table that stores info about jobs with a separate table for each job that pulls together info from elsewhere when a review is conducted. The tables are as follows (and are linked from a paradox DB) :-
Table Name: jobs
JobID (J000001, J000002, etc)
Status (Live, Filled, etc)
Review Tables
Table Name: J000001 / J000002 etc
Consultant: (Joe, Terry etc)
ObjectID: (RoberI, SmithJ etc)
Status: (H, P, D, R etc)
The jobs table contains information about jobs, including a unique code (JobID) that identifies the job. There is also a status filed that tells us whether the job is Live or closed etc.
The first time a job is reviewed a new table, a review table is generated, and the name of the table is the same as the JobID for that job. So Job J000001 has a review table with table name J000001 etc. The review tables may contain information with the same ObjectID (as they are unique fields from a third table – the candidates table)
I would like to generate a union query for all jobs in table jobs with a status of live. I can do this manually, if I review a list of live jobs, with the following sql expression;
SELECT ObjectID, Consultant, Status, "J000001"
as [JobNo] FROM J000001
UNION SELECT ObjectID, Consultant, Status, "J000002"
as [JobNo] FROM J000002
UNION SELECT ObjectID, Consultant, Status, "J000003"
as [JobNo] FROM J000003;
I can then append the info into a new table. However this query is run at least twice a day and things change.
I would like to know is there a means of automatically generating sql for the union query based on results of a query of the jobs table ?
Any help greatly appreciated.
View 2 Replies
View Related
Apr 4, 2005
Hi,
I have hit a brick wall with my A-Level Access Project. Basically, I have a form for ordering where a user can select items from a combo box linked to a table that contains products. They then select the quantity and a subtotal is calculated for that particular item(s). By that I mean the subtotal generated is only for one selection from the combo box. Any subsequent selections have their own subtotals generated afterwards (I hope I'm not being too vague).
This is the form I am talking about:
http://img.photobucket.com/albums/v318/GOAT2G/FrmMakeOrder1.jpg
The thing I am having trouble with is the final box; "Order Total". I have no idea how to set it to sum all of the above Subtotals to equal an overall total. I will send anyone willing to help a copy of the database via email or something if they need it.
I would be immensely grateful for any help you can give.
Thanks,
Adam
View 5 Replies
View Related
Aug 11, 2006
Hi, I have been looking for some answear, but haven't find the proper one!!
I have a form containing a number of records showing the hours worked in format HH.MM at the end of the form I have the sum of the hours, but the result is not correct.
To make it simple to understand, actualy the total of hous for 11.55 and 11.14 gives me the result of 22.69 but the correct result should be 23.09 (23h and 09m and not 22h and 59m)
How can I get the right total???
Marco
View 2 Replies
View Related
Feb 6, 2008
Good morning everyone,
I am trying to have access total a number of invoices for one project to show the total amount invoiced for that project. I would like to have this number displayed in a form so as I enter dollar amounts, the number goes up automatically.
Sorry if this is a noob question but I am in fact a noob.
Thanks in advance
View 5 Replies
View Related
Apr 29, 2015
I am trying to create a database to link to an online auction site (not ebay) and I want to create a query that randomly generates auction end time and auction length for all listings. I have tried int(rnd()) and various versions but can only generate the same number for all fields in the query.
View 1 Replies
View Related
Sep 9, 2013
I have a library function that will allow the user to nominate a query (as one of its arguments) in the calling application which must have an email field. The function will then Do Loop the email field, concatenating it before creating an email and addressing it. The intended functionality is that a developer can easily create a group email, just by creating a query.
This works fine if the query is filtered "statically" - i.e. I specify which group of people by typing in their "Site_ID" in the criteria. However I want developers to be able to creating dynamically filtered queries (perhaps by the group's ID on a calling form). Within the query (to test it), the filter is therefore [Forms]![Test Function Calls]![Site_id]. When I run the code, I am then presented with "Run-time error 3061: Too few parameters. Expected 1". The code in question is:
Dim rst As DAO.Recordset
Dim stTo As String 'one of the function's arguments received from the calling function.
Dim stToString As String 'the built up concatenated emails
Set rst = CurrentDb.OpenRecordset(stTo, dbOpenDynaset, dbSeeChanges)
[Code] ....
View 6 Replies
View Related
Sep 2, 2005
I am making a simple tabular form to use as a subform in another.
The form has a cbo box (cboItem_Name)
Quantity (txtQuantity)
Price (txtItem_Cost)
Extended Price (txtExtended_Price)
Selecting an item in the combo puts the price in txtItem_Cost. Default for Quantity is 1. Control Source for txtExtended_Price = [txtQuantity]*[txtItem_Cost] and gives me my extended price.
I have a control in the footer of the form called txtSub_Total. It's Control Source =SUM([txtExtended_Price]).
This Sub Total box generates the #error.
Can anyone tell me why? Do I need to do some other magic with my txtExtended_Price control instead of a simple calculation, like I have?
Thanks for any help you can give a novice.
View 2 Replies
View Related
Jan 12, 2005
I have a control on my main form which attempts to total the amount from all subrecords on that record. For example, I have fields called ValueA and ValueB in the subform. If I have 5 subrecords in a record, then I want the control to sum all 10 fields (5 for ValueA +5 for ValueB). How can I do this?
View 14 Replies
View Related
Oct 19, 2006
How do I calculate a filtered total from a field in a subform. It needs to be filtered according to [agreementID] and I would like to total the records in another field.
[agreementID] [assessment]
01-pmg18 $20,000
01-pmg18 $15,000
02-pmg18 $13,000
02-pmg18 $21,000
eg: I would like the total assessment for all 01-pmg18's and the total for all 02-pmg18 etc. Can I automate this so it recognizes and totals all assessments according to each unique agreement id group. Customers can have many 'batches" of agreementID's belonging to him. The 18 also refers to his ID number it is typed in. Thanking you in advance!
View 1 Replies
View Related
Sep 20, 2006
Hi there.
I am trying to create a query to show the sum of monies received. However, where there is no money received instead of showing the value as £0.00 it comes up blank.
How do I get it to show it as £0.00 when run?
Regards
Pete
View 2 Replies
View Related
