This Should Be Simple (marge 2 Queries)
Jul 11, 2007
I have one query that returns a list of all debits in a given year/month/cost element: e.g.
Year Month CE Debit
2007 6 111 100.00
2007 6 153 150.00
2007 6 157
2007 6 294 75.00
and another that returns a list of cost element budgets: e.g.
Year Month CE Budget
2007 6 111 500.00
2007 6 120 75.00
2007 6 153 200.00
2007 6 157 120.00
So you can see there could be budgets with no debits against them, and there could be debits with no budget (either could be zero or null). What i want is to merge the two so that I return ALL possibilities: e.g.
Year Month CE Debits Budget
2007 6 111 100.00 500.00
2007 6 120 75.00
2007 6 153 150.00 200.00
2007 6 157 120.00
2007 6 294 75.00
For the lif of me I can't get this to work - I can get all budgets, or all debits, but not a merge of both. Can someone please suggest a solution or point me in the right direction?
View Replies
ADVERTISEMENT
Aug 20, 2006
I'm used to doing a data analysis but never had much cause to use Access previously so I'm trying to quickly pick up the essentials for a specific work project:
I need to extract a subset of data from an Access 2000 database and then perform some category operations on that subset - all interactively. So 2 questions please:
1. I'd prefer to do this with two successive queries (just so the logic is plainer to me) the second operating on the results of the first. But I can't immediately see how to submit the results of one query to a second query interactively.
2. If I were to combine both queries into one can I safely assume that the columns will be processed from left to right (as shown in the query designer)? To explain: I want first of all to select a subset of the data and then to do some stats (ie as a 'total' field) on the subset. But I need to ensure that the stats are done on the subset and not the full original table.
TIA
John Dann
View 3 Replies
View Related
Aug 20, 2013
I use three tables to pull my data.
WorkTracker - each record is a date, worker name, and chore name completed
Chores - list of chores and the allowance received for each
Transactions - date, child, and money spent
I have a query that pulls the sum of transactions and the sum of allowance given but I don't know how to add them together.
Code:
SELECT DISTINCTROW WorkTracker.Worker, Sum([Allowance]+[MoneyTransfered]) AS Balance
FROM Transactions INNER JOIN (Chores INNER JOIN WorkTracker ON Chores.[Chore] = WorkTracker.[Chore]) ON Transactions.Customer = WorkTracker.Worker
GROUP BY WorkTracker.Worker;
View 1 Replies
View Related
May 3, 2013
Have created a simple data collecting database with a simple query to narrow down some of the data , the DB all works fine and some queries are ok, but one the simple query where I want to look a one single set of data.Using the Like "*"&[Enter Search Parameter]&"*" gives me no results.
View 9 Replies
View Related
Feb 25, 2014
I have been trying to understand which method to use for looping through excel cells and storing these into access tables. However, I am having difficulty with so little knowledge in vba.
I have lets say two tables (rows for each table are not fixed) in a worksheet and I want to loop through these rows and store each tables (PROJECT PLAN 1 and PROJECT PLAN 2) in a separate table in access.how to loop through the PROJECT PLAN 1 and PROJECT PLAN 2 in excel and store these in table1 and table2 in access
I have these in excel
B3 I have "PROJECT PLAN 1"
B4 COMPANY | C4 DESCRIPTION | D4 TIME
B5 Google | C5 aaa | D5 10
B6 Microsoft | C6 bbb | D6 11
B7 IBM | C7 ccc | D7 12
next row 8 is blank row and columns
B9 I have "PROJECT PLAN 2"
B10 COMPANY | C10 DESCRIPTION | D10 TIME
B11 Google | C11 aaa | D11 10
B12 Microsoft | C12 bbb | D12 11
B13 B14(merge cell) IBM | C13 ccc | D13 12
B13 B14(merge cell) IBM | C14 ccc | D14 12
This is the code I have so far:
Code:
Dim objXL As Object
Dim xlSht As Object
Dim xlWB As Object
[code]....
View 6 Replies
View Related
Sep 19, 2013
I have a simple query between two tables joined together by common fields. In my first table (Table 1 - tblLocations) I have information about a building i.e. Location Code, address and total sqft. . In my second table (Table 2 - tblAllocatedSpace), this contains details (Location Code, Room ID, SqFt assigned, etc.) of the space allocated in each building. The two tables are joined together when the “Location Code” in both table match.
In my query, I show the location detail from (Table 1 - tblLocations) and related records from (Table 2 - tblAllocatedSpace). My result looks like the following:
Location Code Sqft Address Assigned Sqft
106067 1,000 600 March Rd 10
106067 1,000 600 March Rd 15
106067 1,000 600 March Rd 12
106067 1,000 600 March Rd 20
The location code, Address and Sqft is rebated each time a space is assigned in (Table 2 – tblAllocatedSpace). When a build a report and need to sum the location Sqft, the number is multiplied by the number of related records in (Table 2 – tblAllocatedSpace). In this example by building total space is 4,000 sqft when I only it to show 1,000.
How do I set to only show the location code and sqft once?
View 1 Replies
View Related
Jun 1, 2015
I have an table that contains StudentID, Name and Age. I have imported the data from Excel sheet and there are some records which contains Null Value and some "h", "n/a" etc. I would like to design query which finds the records that are non numeric.
View 2 Replies
View Related
Sep 22, 2014
I have a combo box on my form that passes criteria to a simple select query. There are four possible selections to make from the combo box. For some reason, when I select the first option on the list the query runs perfectly. However, if I select the second, third or fourth option from the combo box, the query returns no records, even though I know there are records in my table which should be returned.
View 5 Replies
View Related
Sep 24, 2013
I am trying to run a simple update query to copy data from one column (Addrl1)to another column (Working_Addrl1) within the same file and I can't for the life of me figure it out. Then I need to repeat for addrl2 and addrl3 to working_addrl2 and working_addrl3.
View 7 Replies
View Related
Dec 29, 2006
Ok I am right now making a simple Vendor/Product database to create a line sheet for some sales folks. I have 3 tables: Vendors, Products, and an associate entity Vendors_Products to relate the two. I have a form currently that draws the Vendor Name (primary key) from the Vendor table and the Product Name from the associate entity. This allows me to create new vendors and select current product types from a drop down box. The problem is that the drop down box is too long and it is tiresome when 1 vendor has 10 product types.
Can anyone tell me how to resolve this? I thought it would be better to have option buttons and display all available products. Then you could just click all of the option buttons that apply to that Vendor and it would create the relationships...is this possible?
View 1 Replies
View Related
Nov 22, 2005
Hi,
I have looked at some of the threads here and it is clear that many of you are working on a much higher level than me and with a high degree of familiarity with the programme.
I am hoping that someone here is able to give me some advice as I don't find the MS help files digestible.
The task I have is to join 2 databases and produce a table from which I can run a mailmerge.
I have managed to join the 2 databases and I used a customer ID as a common link. (my apologies if the terminology is incorrect)
I now have all the data I require in one table.
THE PROBLEMs I have multiple entries for some of my customers and would like to reduce this to single entries (which is understandable). Please tell me how to do this if you can, and keep it as simple as you can please.
In anticipation I thank you very much.
:-)
View 2 Replies
View Related
Jan 14, 2006
Hi there,
I'm making a report detailing competitors final finishing positions in a competition.
The columns are (1) Place, (2) Name and (3) Points
I have the data for their names and the points they have scored, but how do I make the table enter the record number (starting at 1) in the first field???
Thanks in advance
Col
EXAMPLE
PLACE NAME POINTS
1 John Smith 199
2 Ian Jones 90
View 1 Replies
View Related
Feb 16, 2006
Not sure what the issue is here but i am trying to append a Job number from a main database into two connected databases.
The number is a sequential number i have created and the primary key.
It is an autonumber field(obviously). The format is J0000 and the numbers start from J0001
I am using two different append queries to append them into the external databases tables.
However (and here i think lies the problem) the fields i am inserting them into are text fields with the same format J0000.
When i view the numbers in these tables, they are displayed as J1 or J9. The 0's are missing.
Any ideas.
Thank you in advance
View 10 Replies
View Related
Feb 21, 2006
Hey guys, I'm just wondering how I could limit a field to using from 2 - 20 letters. If I placed the letter "a", I'd get an error saying how at least 2 letters must be there, and a maximum of 20. How would I do this?
Lastly, how can I make it so when I enter data into a form, I can click a button which adds it all into the table?
When I do it normally, it's automatically transferred into the table as I type it into the form.
This is incredibly simple I know, but I am a n00b at Access. ^_^
Thanks guys.
View 2 Replies
View Related
May 18, 2005
OK, I have a strange problem... This doesn't seem to work..... It did... then I made some changes.... Can someone clue me in on what I am doing wrong? A query, simply summing some values in fields... no biggy...... BUT...
The field
TotalHours: ([SundayHours]+[MondayHours]+[TuesdayHours]+[WednesdayHours]+[ThursdayHours]+[FridayHours]+[SaturdayHours])
So why isn't this working?
Can I use a simple VBA statement to sum these fields?
:confused:
View 14 Replies
View Related
Nov 24, 2005
hi all, this is my problem:
i got a table like this
ID time in time out working hours
1
1
2
2
the working hours is [timeput]-[timein]
how to i create a query so that all the ID 1' s working hours will be summed up ?
View 4 Replies
View Related
May 15, 2006
:confused: simple query
Critera : <Date()-30 should show records from the last 30 days ???
and : "status"="active" should show records from the last 30 days that are active ????
or am i totally off the mark here ?
vey new to this be gentle :eek:
cheers
View 14 Replies
View Related
Oct 18, 2006
I am trying to create an IN query (ACCESS) which will firstly show the customers table (specific details) then the orders details should be in the IN query, I formulated this but it won't work:
SELECT s.CustomerID, Address, City, PostalCode
FROM Customers s
WHERE
s.OrderID IN
(SELECT s.OrderID FROM Orders p
WHERE s.OrderDate = '1996')
AND
s.OrderID IN
(SELECT s.OrderID FROM Orders p
WHERE s.OrderDate = '1997');
Here is the one I did to show the customers and orders who made orders in 1996 and 97:
SELECT Customers.CustomerID AS Customers_CustomerID, Orders.CustomerID AS Orders_CustomerID, Customers.Address, Customers.City, Customers.PostalCode, Orders.OrderID, Orders.OrderDate
FROM Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID
WHERE (((Orders.OrderDate) Between #1/1/1996# And #12/31/1997#));
it is probably something simple I am missing here! Sorry to keep asking :(
View 1 Replies
View Related
Mar 16, 2007
but I can't seem to figure it out. All I get is Null values when I run the query.
I have a form with a start text box and a end text box. I use the form name sucessfully in the criteria row so I know there is no problem with the form name. I also know there is no problem with the qry name. Does anyone see anything I am doing wrong?!?!
The query I am working in is labeled [qry_old_summary_date_range]. Just wanted to clarify that is not a problem.
1st Pay Increase: IIf([qry_old_summary]![1st Pay Increase]>=[Forms]![Human Resources]![Start] And [qry_old_summary]![1st Pay Increase]<=[Forms]![Human Resources]![End],[qry_old_summary]![1st Pay Increase],Null)
View 1 Replies
View Related
Mar 30, 2007
and presumably it's only me who's irgorant.
I have a table with dates of deadlines and another with dates of submissions. For each deadline I want to know who submitted first. I figured that all I had to do was compute the difference between the dates and the select the minimum. However, Access rejects the Min() function in the WHERE clause. How do I avoid this?
Thanks a lot !!
View 10 Replies
View Related
Jun 27, 2007
I have a table (imported from excel) that is a pricing grid for blinds. The header row at the top is widths(mm) and the header row down the left is heights(mm). The data is the price. The import process makes the widths the column names.
height 600 800 1200 ...
300 $60 $80 $100
600 $80 $100 $120
900 $120 $140 $160
.
.
all I want to do is to take that table and make it flattened, like this:
height width price
300 600 $60
300 800 $80
300 1200 $100
600 600 $80
600 800 $100
600 1200 $120
900 600 $120
900 800 $140
900 1200 $160
I can't think how to do this in a query - I could take the flat version and create a crosstab to go the other way. Any clues - I don't want to change too much about the way the imported excel file looks becasue I have no control over that (I already discard miscellaneous heading and junk before I import it)
Hope you can help
View 4 Replies
View Related
Jan 15, 2008
My Brain has completely lost all of it's gray matter.
I have two tables tbl_Employers and tbl_Services that are linked via a one to many relationship. Each Employer can have Many Services. The field i am useing in the relationship is EmployerID
If I build a simple select query using these tables I will see all of the employers that have a related record in the services table. Any employer could be listed there many times as per the relationship.
What i want is a simple count of how many employers actually have recieved a service.
Why is this so difficult for me to get my head around.
View 7 Replies
View Related
Mar 5, 2008
this is my query in design viewhttp://img99.imageshack.us/img99/3095/querytc3.th.gif (http://img99.imageshack.us/my.php?image=querytc3.gif)i use this query when entering datai want to know how i can set it up it display all or any one/two/none of the criteria i put into the question text boxes, number surname and state, or even just number, or just state.anyone?
View 1 Replies
View Related
Mar 12, 2008
this might be a crude roundabout way of doing things
but it works as is, and i'm trying to streamline a little.
i have a database that contains all of my customer's details.
once a month remove those whom no longer want my mailout,
and export the remainder into excel so that my printer can print and stuff envelopes.
i record customers that no longer wish to recieve my mailout, by putting something(anything) in a column that says rts.
what i'm trying to work out is, how in a simple query i can prevent any record that has any value in the rts column from displaying the name/address etc.
if there's a code to say
if anything in rts exists then hide name address state postcode
View 2 Replies
View Related
Jan 24, 2005
G'Day:
I am working on a simnple form that has just a few simple calculations in it. One being wieght difference and the other being percentage weight difference.
I get Access to calculate the wieght difference easily, but Ican't then get it to calculate a percentage weight difference (in another cell).
This is what I have down for code.
If Weigh1 <> 0 Then
WeightDiff = Weigh1 - Weigh2
End If
If WeightDiff <> 0 Then
PerWeightLoss = WeightDiff / Weigh1
End If
Any Suggestions?
Thanks
View 10 Replies
View Related
May 15, 2005
:confused: I have a logon form which works well and authenticates users via a table. The problem is when the form is loaded the user has to click into the text box to type their name. I want a cursor to be flashing in the text box when the form opens so the user can type their name straight away without having to touch the mouse. I have ensured that the tab index for the text box is first however this has not resolved. Is it something to do with focus? If so how to I ensure that the text box has focus on launching the logon form??
View 1 Replies
View Related