Queries :: Creating A Row Based On Difference Between Tables

Jul 3, 2013

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

View Replies


ADVERTISEMENT

Tables :: Difference Between Creating A Table Using SQL And MAOL

May 26, 2014

what is the difference between creating a table using SQL and creating a table using MAOL (access object library)? I ran into these 2 methods when taking a tutorial on the internet? What are the advantages of each method?

View 1 Replies View Related

Queries :: Find Difference Between Multiple Fields In Two Tables

May 8, 2013

I have a database with two tables, one for the amount that was estimated in each cost section, and one for the actual amount billed for each cost section. The tables have the same number of fields, all with the same names. They can be linked together with event ID. Each table has over 100 fields and I would like to find the difference between what was estimated and what the actual was for each event. I would also like to see which cost section has the most and least variance. I am trying to do this without going through each cost and putting [tEst].[CostName]-[tActual].[CostName].

View 2 Replies View Related

Creating New Tables Based On Templates

Aug 29, 2014

I am working on a dataBase and I need to have it check if a set of tables exist based on the data of another table and if any do not exist create the table(s) based on a template. the caviat is that the data in one table are both names of tables and field in another.

Table1 has fields named 'first name' and 'last name'; the data in that field is for example Peter Adams, John Smith. I need to check on start that the tables named PAdams, JSmith.and so on exist, if not create them from a table template with fields 'vacation', 'personal', 'sick, etc. also i need to check on startup that a table named Department exist; if not, create it with fields named PAdams, JSmith and so on.... is that possible?

View 1 Replies View Related

Queries :: Creating A List Based On Multiple Queries

Jun 23, 2015

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.

View 2 Replies View Related

Creating A Form Based On Three Non Related Tables??

Jul 17, 2006

Hi

I was wondering how to create a form which is using data from three tables, the data in the tables does not have any relationship setup as they are not related to each other.
When I use the wizard and I select the three tables, Access starts complaining about the fact that no relationship has been setup...

Please help,

Thank you,

Gurkie

View 2 Replies View Related

Modules & VBA :: Creating Queries Based On Value In Checkbox

Jun 30, 2013

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] ....

View 4 Replies View Related

Creating Updateable Datasheet Based On Mult. Queries

Feb 12, 2006

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

View 3 Replies View Related

Queries :: Creating Unmatched Query Based On Selection From Two Combobox

Apr 15, 2013

I created 2 combobox that list specific table in a data base. I want to create an unmatched query based on the selection from those two combobox.

All field in those table are the same.

View 3 Replies View Related

Queries :: Creating A Query With Self Join Tables

Feb 11, 2014

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..

View 1 Replies View Related

Queries :: Creating Query To Pull Info From Multiple Tables

May 7, 2013

I have DB used for inventory for many different categories. I have a table and form for the following: Location, Printers, Pc's and many more.

What i am trying to accomplish is to have a advanced search form that will display how many pc's and there makes and model from selecting the location name or Cost center from a combo box.

So an example would be I want to select MPP-WDF from the combo box click a button and it will return the number of PC and there makes of model's and some other information in a list of records.

View 9 Replies View Related

Date Difference Query Based On Excel Formula

Aug 24, 2015

i want to write a ms access query for date difference.i have the excel formula for that.in attachment i have shown wot i really want as output.i want to make a IIf query for these conditions.

in excel i did this like:
=IF(J13="",IF(INT(B13)=INT(AE13),C13-DAY(I13)+1,C13),IF(INT(AA13)=INT(B13),IF(MONTH(J13 )=MONTH(I13),J13-I13+1,DAY(J13)),IF(INT(AA13)=INT(D13),DAY(J13)-E13,0)))

how to do it in access query.

View 4 Replies View Related

Difference Between Two Tables ??

Jun 2, 2005

Hi, I have two tables, one being a subset of the other. What I want to do is write a query that lists data in the larger table that is not in the smaller one. In effect I want to subtract the data in the smaller table from the larger table and list the remainder.
I have tried something like:

Select a.oem from a,b where a.oem not in (select b.man from b);

This query does not complete and I get 100% CPU usage :( . The largest of the tables has roughly 4000 records in it. Any ideas? Am I doing something wrong?

Thanks

View 3 Replies View Related

Query Difference Between Tables

Mar 27, 2008

struggling with this, hope someone can help.

I have a table of timesheets that contains records for staff but it only contains weekly record if they have actually created that weeks record.
I also have a table of weeks that contains all of the available weeks that time can be booked against.

How can I query the timesheet table against the weeks table and get a list of staff that have not created a record for that week ?

View 5 Replies View Related

Tables :: Difference Between Numeric And Text?

Jul 1, 2013

I have a field that will only get numeric values, numbers. And it will always have four characters or less. I do have times when I worry my data by over eighty's certain size or I average value, or both. Is this possible when using a text field.

View 14 Replies View Related

Queries :: Finding Difference Between Variables

Nov 8, 2013

What I am trying to do is the following

I have three columns

Name, Date, Number

What I need to do is find the difference in the number column where the Name is the same but the date is not ex:

Bob 2013-12-12 123
Bob 2013-12-15 456
Jane 2013-12-14 789
Jane 2013-12-25 987

So what I need to do is to get a result that is independent of date i.e.

Bob (456-123)
Jane (987 - 789)

View 2 Replies View Related

Queries :: Query Count Difference Of Two Fields?

Jul 20, 2013

i have a access table (AUTH) with following field

Company Auth Held

Tata 12

Dell 11

HP 21

Opera 11

Bangour 10

i used the following query to calculate the Held from a table named pers.

SELECT AUTH.company, AUTH.Auth, Count(AUTH.company) AS Held
FROM pers INNER JOIN AUTH ON pers.company = AUTH.company
GROUP BY AUTH.company,AUTH.Auth

then i got the result as under

Company Auth Held

Tata 12 02

Dell 11 08

HP 21 20

Opera 11 12

Bangour 10 12

now i want the difference between Auth and Held as Sur/Defi

how can be it done.

View 2 Replies View Related

Queries :: Database Which Calculate Difference Between Dates

Sep 14, 2014

I have the attached database which calculate the Difference between dates.I have also two problems like i shown in the image

Code:
Option Compare Database
Option Explicit
Sub Recalculate()
Dim rst As DAO.Recordset
Dim dff As Long

[code]...

View 4 Replies View Related

Queries :: Calculating Difference Between A Table And Query?

May 2, 2013

I'm trying to make a query where it can calculate the difference between a table field value and a query field value.

Ex. Table INVOICE - fields
invoice no, invoice value,
Query TOTAL - fields
invoice no total invoice value

I need to get the difference between invoice value and total invoice value.

How can I put this into a query (invoice value - total invoice value)?

View 4 Replies View Related

Queries :: Calculating The Difference In Values In Two Different Records?

Aug 12, 2015

I have a query that must calculate the different between values in two different records, based on the date.

The underlying ORIGVALUES table contains:

Date; Value; Diff
1/10/15; 100; (this is what I am trying to calc)
1/11/15; 101; (this is what I am trying to calc)
1/14/15; 102; (this is what I am trying to calc)

What is the best way to have each record calculate the selected date value divided by the prior date value)? E.g.

For 1/11 the math is 101 / 100 (so the 1/11/15 record would show the result of 1.01)
For 1/14 the math is 102 / 101 (1/11 to 1/14 represents a weekend)

I could copy the prior day values to the current day records and do that math easily but that is very redundant.

View 5 Replies View Related

Queries :: Calculating Difference Between Dates In 2 Rows

Oct 25, 2013

I have a list of client activity - client name, loan ID and loan date. I would like to create a new field that shows the number of days between one loan and the next. If I was doing it in excel, I would need to sort the data by client name and loan date and then calculate the number of days between the loan date of one loan and the loan date of the loan immediately prior to this.

View 14 Replies View Related

Queries :: Date Difference Between Rows In Same Column?

Apr 20, 2014

I have the below query (A). How do I calculate the Day of Therapy as shown in B

A
MRN Last Name DOA Service startDate INR Dose WarfID VisitID
12001 Smith 6/28/2013 Warfarin 6/29/2013 1.20 6 mg 10 138
12001 Smith 6/28/2013 Warfarin 6/30/2013 1.20 6 mg 11 138
12001 Smith 6/28/2013 Warfarin 7/1/2013 1.3 6 mg 12 138
12001 Smith 6/28/2013 Warfarin 7/2/2013 1.40 6 mg 13 138
12001 Smith 6/28/2013 Warfarin 7/3/2013 1.40 7.5 mg 14 138
12001 Smith 6/28/2013 Warfarin 7/4/2013 1.70 7.5 mg 15 138
12001 Smith 6/28/2013 Warfarin 7/5/2013 2.00 7.5 mg 16 138
11002 Brown 7/1/2013 Warfarin 7/1/2013 1.00 2 mg 17 139
11002 Brown 7/1/2013 Warfarin 7/2/2013 1.3 4 mg 18 139
11002 Brown 7/1/2013 Warfarin 7/3/2013 1.50 2 mg 19 139
11002 Brown 7/1/2013 Warfarin 7/4/2013 1.8 2 mg 20 139
11002 Brown 7/1/2013 Warfarin 7/5/2013 2.20 2 mg 21 139
1205972 Jones 6/28/2013 Warfarin 7/6/2013 1.0 6.5 mg 29 138

[code]....

What I really want is to calculate the time (# day) it takes to reach a target goal INR (2-3) I have tried DateDiff between DOA (date of admission) and Start date but this obvious won’t work because some patient won’t start on warfarin until later.

Another possibility is to calculate date difference between two rows in same column but I don’t know how For example Pt 1 = It take 7 days to reach an INR of 2, Patient #2 = it takes 5 days to reach an INR 2.2 and Patient #3 takes 6 days to reach an INR of 2.1. Average = 6 days.

View 6 Replies View Related

Queries :: Number Of Hours - Calculating Difference Between Times

Jun 23, 2014

I am starting to create a resourcing database that needs to be able to work with dates and times and perform calculations on them.

I need to work out the number of hours. I have two date/time fields Day_Start_Time and Day_End_Time. I want to calculate the difference between the two in hours and then multiply by the number of working days.

I tried this: Number_Of_Hours: DateDiff("hh",[Day_Start_Time],[Day_End_Time])*[Number_of_days]

But get #Func! when I run the query.

View 3 Replies View Related

Queries :: Concatenate - Difference Between Numeric And Text Fields

Aug 6, 2015

I have a Query contains field that is :

Code : AllNv: Concatenate("SELECT NAll FROM NormalsQ WHERE SerName='" & [SerName] & "'" & " ORDER BY NormID")

but [SerName] is a text type field that is not Primary Key.

I have in the FamilyTbl , [ServiceID] is numeric type field, and a primary key. I try :

Code ; AllNv: Concatenate("SELECT NAll FROM NormalsQ WHERE ServiceID='" & [ServiceID] & "'" & " ORDER BY NormID")

but this returns all [NAll] records. I use concatenate :

Code:

Function Concatenate(pstrSQL As String, _
Optional pstrDelim As String = ", ", _
Optional pstrLastDelim As String = "") _
As Variant

[Code] .....

View 14 Replies View Related

Queries :: Difference Between Rows - How To Extract CHANGED Value In A Column

Jan 24, 2015

I have a query question . How can I extract a CHANGED value" in a column? Example here under table1. Time and TAG makes a record unique, No ID available ...

Table1
time tag value
10:02 Tag1 10
10:02 Tag2 11
10:03 Tag1 10
10:03 Tag2 11
10:04 Tag1 10
10:04 Tag2 12
10:05 Tag1 10
10:05 Tag2 12
10:06 Tag1 10
10:06 Tag2 12
10:07 Tag1 11
10:07 Tag2 12
10:08 Tag1 11
10:08 Tag2 11

I want to have as output
10:04 Tag2 12
10:07 Tag1 11
10:08 Tag2 11
(so at 10:04 tag 2 changed in value and on 10:07 tag1 changed value and 10:08 changed Tag2)

Preferably with the previous value as well, but this is not the highest prio
10:04 Tag2 12 11
10:07 Tag1 11 10
10:08 Tag2 11 12

View 2 Replies View Related

Queries :: Date Difference Excluding Weekends And Holidays

May 6, 2014

Is there a way, in a query or via the use of a module, to calculate the workdays between two dates excluding holidays without needing to maintain a separate table with the holidays listed out?

For example, I would like it to calculate 21 work days for the month of May. However, I don't want to have to go in the first of each year and manually list out all the holidays for the year. Is there a way for the Holidays table to just contain the number of holidays in a given month (i.e. in December we get 3 holidays (Christmas Eve, Christmas Day, and New Years Eve).

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved