Sum Of Quantities In Three Tables
Jul 27, 2005
Greetings to you all
I have been struggling with a problem I thought was simple. However I am unable to solve it. I have searched the forum with no luck.
I have three separate tables that have somewhat similar structures. Each have a Quantity in a specific column that I would like to sum across all three tables. The tables contain hourly values for (say) one week (168 values for each contract type). The table structure is as follows:
T1:
Contract_key1
Effective_Date
Quan1
T2:
Contract_key2
Effective_Date
Quan2
T3:
Unit_key
CC_key
Effective_Date
Quan3
The length of T1 and T2 depends on how many contract types are involved. Table T3 depends on the number of units (Number of rows= 168*No. of contracts(units in T3)).
I would like to write a query (will be in VB eventually) that would give me the Sum(Quan1+Quan2+Quan3) for each hour accross all contracts and units. My output table should have only 168 rows (sum all quantities each hour). In the future I may want to sum for each contract, but currently I only need a single sum for each hour.
If this issue has already been dealt with on this forum please point me in the right direction.
Thanks
rmiller
View Replies
ADVERTISEMENT
Mar 21, 2012
Let's say I have a table with information like this:
Number Quantity Date
1 20 XXX
2 12 XXX
2 65 XXX
1 6 XXX
4 13 XXX
5 32 XXX
3 41 XXX
4 22 XXX
Now, Let's say I want a report as shown below..
Number
1 Total= 26
2 Total= 77
3 Total= 41
4 Total= 35
Well, by now I'm sure you get the point. I want it to add all the quantities with their respective numbers and only show the total on the report. How would I do this?
View 2 Replies
View Related
Sep 7, 2007
I have a field, quantity, which i want to add all the quantities together to show a total. I want to do this using a query as i will use this total for other calculations. When in the query, i have selected "sum" in the totals column in the quantity. What do i need to do extra? I am not getting any results at the moment.
Emily
View 2 Replies
View Related
Jan 22, 2007
I used the Northwind database and modified it to suit my needs. This is basically just maintaining inventory, using the "Orders" form and "Orders Subform" to create records in the "Orders" and "Order Details" table which deduct quantities from the "products" table.
Our employees are allowed to use the "Orders" form to issue items from inventory, and this is the method I used for updating quantities in the "Products" table (method 1):
http://support.microsoft.com/kb/252813
Here's the problem: It is entirely possible that a transaction from inventory might be cancelled, and there is no way to replace the inventory levels in the "Products" table, other than doing it manually.
I would prefer a method of maintaining inventory levels in the "Products" table based on current records in the "Order Details" table, whereas, I could merely open the "Order Details" table, delete the canceled transaction (record), and the quantities in the "Products" table would be automatically adjusted.
It doesn't seem like this should be too difficult, I just need a point in the right direction on how it should be done.
Any help will be appreciated.
Thanks,
Mark A.
View 1 Replies
View Related
Jun 30, 2005
Hi
I am creating an invoice which has a subform of the products which the customer may purchase.
So within the invoice itself there is:
ProductID | Product_Name | Quantity | Unit_Cost | Line_Total
I am hoping to have the Product_Name as a drop down of all the products which are available and would like the Unit_Cost to automatically lookup the unit cost of the respective Product.
Could someone please help me as i dont know how to do it, at the moment its a case of the individual having to look at the unit cost within the Product_Name drop down, remember it and then enter it lol
Also, how do I have the Line_Total as a calculated field of the Quantity*Unit_Cost? Do I put it within the Order Details table (which handles the many-to-many) or within a query?
Any help or links directing me to answer would be great
Many thanks in advance
:: CNLIFEASITIS
View 2 Replies
View Related
Aug 6, 2014
I have three sources of data which all equate to a reconciliation of the same data e.g. the same record of a stock holding is held on three separate systems, which must all be the same.
I am trying to build a an equation in a query field, which compares each quantity:
Qty Diff: [Dealer Qty]-[Pulse Qty]-[SWFAL Qty].
If the quantities are all the same, then I want to return a Zero. If one or more of them are different, I want that difference to show. Variance of the above equation that would do it?
View 5 Replies
View Related
Jun 10, 2014
I need to run a condition that compares 2 field quantities together based on the same item. If they both equal the same then i want it to run a code and change a property for that record.
I will have a OrderQty and a Total receivedQty when these 2 equal each other then it updates the items Status to Received. What is the best way to accomplish this? in vba or queries/sql?
View 4 Replies
View Related
Sep 30, 2014
I have a transactions table which records a qty number (- or +) and references particular location details (location, room, rack, shelf etc). My question is how do I sum the qty for the same product at different locations. So my user sees that product A has a qty of X in location C and qty Y in location D. I can sum the transactions table qty which does give me a sum based on a single location but each location has multiple racks, shelves etc. I need to know the sum of transactions for the same product for exactly the same location, room rack etc
View 3 Replies
View Related
Nov 2, 2014
I have a form with subform . I want to calculate purchase quantity with criteria of product and quanties before sale date. If i use with specified date it gives correct result.but if i use field address it ignores date criteria
Nz (dsum ("pqty","purchasequery","product=" & [sales.product] & "and clerancedate<=#31/07/2014#"),0)
Nz (dsum ("pqty","purchasequery","product=" & [sales.product] & "and clerancedate<=#" & [forms!salesm!sdate] & "#"),0)
But it is not working
Sdate is date and it is on main form
View 6 Replies
View Related
Sep 9, 2014
I have attached a sample of a database.
Table 1 has all the items I am trying to sell with sell by date after which I cannot sell this item. Then in Table 2 I have forecasted sales. So now I am trying to calculate stock consumption to see if I will be left with any stock that I cannot sell.
So now somehow I need to deduct sales forecast from my stock holding but it needs to go by date i.e. consume all stock for Item 1 with date 16/09 before moving to Item 1 with sale by date 23/09.
So based on the attached example, I can see that on 16/09 I will consume only 5 cases from sell by date 16/09 and another sale is 18/09. So that would give me information that I will be left with 95 items dated 16/09, which I cannot sell because they will be out of date.
Ideally I would like also to include the logic that if Item is out of date it would move to the next sell by date.
So in this case sale of Item 1 forecasted for 18/09 (94) would consume the whole stock (50) with date 23/09 and another 44 from date 01/10
For Item 2 I can see that units with Sell by date 30/09 will be consumed on 25/09 and I will start taking stock from next sell by date which is 14/10.
View 8 Replies
View Related
Sep 26, 2012
Currently trying to build a database for customer management and order placement/tracking. Want to set a couple of rules so that if I for instance click yes of billing and shipping address the same that the database will automatically fill the shipping address with the data I inputted for the billing address in the same table.
The other issue I can see I'll run into is, I want to be able to select one of the company ID's (made up of a three letter abbreviation of the full company name) in the product ordering table and it will automatically fill in the rest of the customer data (phone, email, address etc) data into that form.
View 2 Replies
View Related
May 30, 2014
What I have is a database setup with multiple tables in which different areas of my DC can input information simultaneously into their respective tables. I then have another database linked to it for myself to have a live view of each updated record. I would like to see all the records of each table in 1 single table (possibly just sorted ascending by time). Each table has the same Field headings but may have different qtys of records. As I will then have it linked to an Excel table to VLOOKUP from it.
I have tried Union coding but always get Syntax Error etc.
View 8 Replies
View Related
Dec 3, 2012
I have 2 identical database in terms of structure but it differs in data.
Basically I would like to import data from subDatabase to mainDatabase and ensuring that there are no duplicate records.
I have used the "link to datasource method" through importing the tables to have the "updating" function.
However, this method also means that the records in mainDatabase are also imported over to subDatabase which I do not want.
Is there a method to ensure that the records are shared/update one way only? (i.e. import from subDatabase to mainDatabase and not main to sub?)
View 1 Replies
View Related
Feb 10, 2015
I'm using the MS Access 2010 ExportwithFormating action to export three tables to a single MS Excel 2010 workbook. The action overwrites the first excel worksheet each time instead of saving all three worksheets in a single excel workbook.
How can I export three tables into a single excel workbook.
View 1 Replies
View Related
Jan 14, 2013
I have created a table that acts as a header for my data and a second table that acts as line item data. What I need to do now is add a second child table that uses the line item data as its parent table and stores associated line items for each record. Is this possible?
This is a skeleton view of what I'm going for:
Master Table:
tlbAuditReportHeader
- AuditDate
- AuditArea
- Auditor
Sub Table:
tblDiscrepancy
- Discrepancy
- CorrectiveAction
- ActualCompletionDate
- VerifiedDate
- Notes
Sub table to Sub Table
tblFollowUp
- FollowUpDate
- AssignedTo
- SpokeWith
- EstimatedCompletionDate
- Notes
Sometimes tasks change hands or are pushed back depending on work load. It would be nice to be able to track something like this.
View 1 Replies
View Related
Apr 23, 2013
I am using calculated field as a data type in access 2010.
They are working fine.
However, I added a new field and now the final calc won't work.
I have Subtotal adding loads of fields together. Works fine.
Then I have a VATunit field which is a double integer, so enter 20 and my next field is VATTotal calculates the SubTotal + the VATunit by doing (Subtotal/100)*VATunit. This calculation is fine and gives me the correct amount.
The next field is a Total field. Which adds Subtotal and the VATTotal together. Howver, the Total is the same as Subtotal. It is not adding the VATTotal to it?
View 2 Replies
View Related
Apr 19, 2007
I have some experience doing 'Update Query' using two different tables but I'm having a hard time doing an 'Update Query' using 3 tables.
I have my source table TP05XY with the fields 'Mark' 'Date' 'UTM_Edig' and 'UTM_Ndig'. Mark and Date are my primary keys (they together uniquely ID each record). I have my Observations table with the fields 'Mark' 'Date' and 'Obs_ID'. The last table is Locations with 'Obs_ID' 'UTM_E' and 'UTM_N'.
I want to update my fields UTM_E and UTM_N from UTM_Edig and UTM_Ndig. However, to do so, I have to go from my TP05XY table, through Observations table to update Locations table. Table TP05XY is joined to Observations through 'Mark' and 'Date' and Observations table is linked to Locations through 'Obs_ID' field.
I have tried a few options without success ... anyone knows how to do it?
Thanks,
Josée
View 1 Replies
View Related
Jul 19, 2014
I have being playing with ms access but I really don't know much about it or databases in general.I have created a very simple database to gather twitter following/followers data for research purposes.One table (table01) has a field for the "boss" user (=the user who I gather data for), another field for "client" (=bosses followers or friends).Both fields are numeric and contain the users id's.In order to distinguish if the link is follower or friend there is a third field, called type which can be either 1 (=follower) or 2 (=friend).So the data would look like this:
boss - client - type
12345, 67890, 1
12345, 54321, 2
If user with user id 12345 had a follower (type 1) with user id 67890 and a friend with user id 54321...In order to avoid getting duplicate rows I also added a unique identifier which is of the form boss_id-user_id-type.So the above row looks like this:
12345-67890-1, 12345, 67890, 1
12345-54321-2, 12345, 54321, 2
That works just fine.For several reasons I also needed data of the form source - target.So I also made another table (table02) of this form.
67890, 12345
12345, 54321
...
In table 2 you don't need the "type" field since the position of the user id shows the type of relationship.Still, you need a unique identifier in order to avoid duplicates, so I added on with the form: source_id-client_id..So table02 lookes like this
67890-12345, 67890, 12345
12345-54321, 12345, 54321
...
Both tables also have a date/time stamp for each line.As you can see, table01, having also a type field is bigger than table02.The problem is when I try to append data, exactly the same data in both tables.Appending data to table01 is ok, while appending data to table02 (which is smaller, having one less field) takes a really long time, maybe 10 times as long as appending data to table01.To make sure that no query's are causing the problem I have tried first creating temporary tables with the data to be appended, no duplicates, nothing that would cause the database to make extra calculations and used a simple update query with no filters to append data.Still I get the same result, table02 takes a very long time to finish while table01 finishes in no time.
View 2 Replies
View Related
May 13, 2015
I am currently using Microsoft Access 2010 32bit, and have one database acting as parent, with a second linked database as a client for people to work with, and the parent database has tables imported from Sage V21 via ODBC. I have used the following code as specified in other examples as follows:
Code:
DoCmd.TransferDatabase acImport, "ODBC Database", "ODBC;DSN=MyDNSMachineName;UID=MyUID;PWD=MyPassword;LANGUAGE=us_english;" & "DATABASE=pubs", acTable, "INVOICE", "INVOICE"
one of the tables has 10k records in it, it only transfers 77 records. After manually attempting an import via the ODBC wizard it finally lets me access all 10k records.I wish to have a single button click delete and import fresh tables without worrying if all the data is coming across.Also, when manually adding a table, I am usually asked by the wizard to specify an index, but with the code above, I am not offered that option and the tables come across with no index. I am led to believe that having tables that link to each other without an index is bad, so how do I ensure an index is created?
View 5 Replies
View Related
May 29, 2014
I have two tables of data, each relating to three business branches (branches A, B and C).
Table 1 shows the expenditure of each branch (by fuel, premises and wages).
Table 2 shows a number of units for each branch (mileage, floorspace and sales).
What I would like to do is calculate unit costs, based on the expenditure in Table 1, divided by a relevant unit in Table 2. The catch is that I want to have a third table which allows the user to specify which expenditure (from Table 1) is combined with which unit (from Table 2) to generate the calculated unit costs. I've been able to do this in Excel, and have attached an example. I've also attached an incomplete Access version with the first two tables. Given the complexity of my actual data, I feel this could be better handled in Access than Excel.
View 6 Replies
View Related
Feb 18, 2013
I have an Access Table with about 28,000 Automobile dealerships across the country shown. I've joined a new/small phone contact table to this to keep up with our phone contacts with the dealership and followup efforts. When I search/filter on the dealership table all is fine. However when I search/filter on the phone contact table with a few test entries, I get nothing at all. I supposed that after joining the tables, I'd be able to do a search on the field named follow up date and find/filter today's date or other dates and locate which dealerships to contact when the correct date arrives. But nothing.
View 14 Replies
View Related
Jul 24, 2014
I have developed a Microsoft Access 2010 database for my client and the database is split with Front-end/Back-end, the Back-end and the database is shared on Network, The client operating system and applications for all users are hosted and consistent and the service is delivered over Citrix.
The database some times corrupt the tables record and give a permanent #Delete Error, I have attached one of the database table and the screenshot of the error,
View 3 Replies
View Related
Sep 26, 2012
I want to fill local tables in some application with disconnected recordset. The tables in the front end application having the same table structure as in the back end database. The front end application was linked with the back end password protected database tables. I want no connected linked tables in the front end application. How can I fill the local tables in the front end application with the back-end password protected tables?
View 8 Replies
View Related
May 5, 2013
I have 3 tables.
Table 1: contains staff names and contact numbers
Table 2: contains training above staff have been on or need to go on
Table 3: contains pc and printer asset numbers of above staff
I used a form and entered some new members of staff in table 1. They got their auto numbers etc but when I open table 2 and table 3 those new members are not showing up in those tables. I have checked the relationship status between the 3 tables and the staffID from Table 1 is associated to table 2 and to table 3.
What's stopping the new entries from showing up in tables 2 and 3 ?
View 10 Replies
View Related
May 28, 2013
I am splitting a database and have created the Back end already. When I create the front end and link to the tables on the back end... The front end does not link to all the tables in the back end. The list that comes up when creating the linkings does not show all the tables in the back end. What would cause this?
View 1 Replies
View Related
Oct 23, 2013
I am working on a database which has two tables used as part of a registration and login process.
I would like a couple of fields from table one to automatically update in table two, once the fields in table one are populated without using an 'on click' event.
The reason I would prefer not to use an onclick is because the completion of the form used to generate the users table does not require any buttons for the data to save.
View 1 Replies
View Related
