Create 1 Query Instead Of Many
Aug 20, 2007
hello!this is my first time in this forum
i hope you can help me with this...
i created a database wich has one table with many fields, what i need is a parameter query to sort the information on that table, the problem is that i want the user to decide wich fields he needs, and if he want to use parameters (criteria) or not on those fields he choose, i wanna now if this is posible with just one query instead of one for each combination of fields the user wants on his query. i hope i make myself clear if i dont just let me know
oh and the criteria is taken from comboboxes in a form, and the query is runned from a comand button on that form too.
thank you
View Replies
ADVERTISEMENT
Dec 8, 2007
In database is one Table, where are only two fields (ID and Text):
ID Text
1 AA
2 BB
3 AA
4 CC
5 EE
And in a form I have TexBox where is a text string, for ex. : aa; cc; ee
Im trying to create Query which find all records from Table which contains
"aa" or "cc" or "ee".
If I use concrete values in Query, it works :
SELECT * FROM Table WHERE (((Table.text) In ("aa","cc,ee")));
but when I use refer to a TextBox it doesn't works :(:
SELECT * FROM Table WHERE (((Table.text) In ([TexBox])));
Because it will do [TextBox]="aa;cc;ee" and not "aa";"cc";"ee"
Can anyone help me with it?
Thank you and have nice day.
Jira
View 6 Replies
View Related
Nov 29, 2005
HI,
I'm trying to create my own database from queries. the queries retreive data from and ODBC data source. How do I create table from query but I don't want any old data from table to be deleted whenever there's a change of data in ODBC data source. I just want to keep pushing in new information without deleting the old ones, even though those old information have been deleted from the ODBC source.
thx guys!:D
View 1 Replies
View Related
Dec 24, 2006
Hi All,
I have 2 tables like below:
TableA:
- Customer
- Customer Industry
Table B:
- Customer
- Project
Each customer has many projects.
My ultimate goal is to create a table like this:
Table C:
- Customer
- Project
- Customer Industry
Trouble is, tables A and B are updated by different persons. A project is usually created first before someone creates the entry in Table A to enter the customer's industry. If someone forgets to update Table A, I will see entries in Table C with industry of customers as blank - which i don't want to.
What is the best way to do this?
I'm thinking of running some sort of queries that will automatically create an entry in Table A when a new customer project has been entered in Table B, so that the person responsible for Table A knows to enter in the industry - but i don't really know how to start, could you help please?
Merry x'mas all!
View 1 Replies
View Related
Jul 25, 2007
:) Hello,
Can anyone please tell me how to create a graphic (a chart like in excel) based on the result of a query? It's just names and values (2 fileds only)
Thankyou.
View 3 Replies
View Related
May 31, 2005
Hi
Is there a quick way to split up a table into many other tables? I want to split the table by Client Number, so all the Clients with 1234, for example, are in a table called Client1234
I know the very long winded way, creating a ‘Create New Table’ query, inputting the Client Code criteria each time.
Is there a better more efficient way?
Thanks
coley
View 8 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
Sep 7, 2005
Hey guys, I am trying to creat a query from my Status table, but having NO luck getting it to work. Basicly the Query is going to create brand new columns with the Status entry from the row selected. I have been able to create a query with 1 status column, but I cann't seem to figure out how to code it where it creates a 2nd. Heres what I have
SELECT SiteStatus.Status AS MailStatus
FROM SiteStatus
WHERE (([SiteStatus].[Site Component]="mail"));
Some how I need the Where statement to go along with the new Mailstatus column and then create a new column with the same effect, have it select the status from the row what contains the "component" in the "component" col. Anyone get any of this cause its starting to confuse me even now. Using Access 2003
View 2 Replies
View Related
Sep 11, 2006
Trying to do this sort of query is beyond my comprehension of the software, so hopefully I can get help here.
What I am trying to do is add a total of a certain field together, while not adding the total of another field together. I hope that makes sense.
Anyway, here's an example:
Family: Sum of Duration: Years: Total Birds per Hour:
Tyrannidae 10 1999 0.1
Charadriidae 107.35 1996 26.967
Charadriidae 175.05 1997 45.29
Rallidae 34 1995 46.15
What I am trying to accomplish is that I want to add together the Total Birds per Hour field, without having the duration sum together. What has been happening is that when I sum TBpH, Duration sums, also. Yes, I know I have it as sum of duration, but the point is that has -already- been summed and therefore does not need to be summed again.
Basically, we are trying to see how many birds appear per hour within each year. However, our Duration totals cannot exceed a certain amount per year. Say, we can only get 600 hours in the year of 1997, but instead I end up getting something like 7000 hours because each entry in the family "Charadriidae" gets summed together when it should not.
What was explained to me was that, when we are summing the durations together, only one duration entry at a specific date should be added together. Here is what we believe is happening:
Date: Duration: Species:
199534B 5.5 Ruddy Duck
199534B 5.5 American Avocet
Theoretically, as a total duration for the date "199534B", we should only be getting 5.5, because both of those birds were spotted during a 5.5 hour period, not an 11 hour period if you were to sum those up.
I know that sounds complicated, and I'll try to explain more later. But, are there any suggestions on how I should go about this?
View 6 Replies
View Related
Dec 7, 2006
How do you create a query in code. Actually creating a query that appears under the query section of access. Is this possible?
Thank you
View 2 Replies
View Related
Dec 11, 2006
Hi Guys!
It's me again.
From one of my tables, custinfo, i want to generate a summary of records. What I mean is I want to count the total number of records, count those who doesn't have address entries, count those who doesn't have credit records. So in presentation, I want to have this:
Field No Records With Records Total
Address 10 256 266
CreditREc 5 261 266
Is it possible?
Thanks!
View 3 Replies
View Related
Mar 9, 2007
Hi,
I wrote a script which creates a new table and append records into it. Here are my codes:
For Each rst In dbs.tabledefs
If rst.NAME = "Biweekly_Temp_Table" Then
dbs.tabledefs.Delete rst.NAME
dbs.tabledefs.Refresh
End If
Next rst
qryBtt = "SELECT DEDPARMS" & payp & ".EMP_ID, DEDPARMS" & payp & ".FORMAT_NM, " & _
"Sum(IIf(Right([DEDPARMS" & payp & ".Dedtype_CD],1)='R',[overded_am],0)) AS [Employer Amt], Sum(IIf(Right([DEDETAIL" & payp & ".Dedtype_CD],1)='R',[DEDETAIL" & payp & ".ded_am],0)) AS [Employer Actl], " & _
"Sum(IIf(Right([DEDPARMS" & payp & ".Dedtype_CD],1)='A',[overded_am],0)) AS [Admin Amt], Sum(IIf(Right([DEDETAIL" & payp & ".Dedtype_CD],1)='A',[DEDETAIL" & payp & ".ded_am],0)) AS [Admin Actl], " & _
"Sum(IIf(Right([DEDPARMS" & payp & ".Dedtype_CD],1)='E',[overded_am],0)) AS [Employee Amt], Sum(IIf(Right([DEDETAIL" & payp & ".Dedtype_CD],1)='E',[DEDETAIL" & payp & ".ded_am],0)) AS [Employee Actl], " & _
"First(DEDPARMS" & payp & ".STATUS) AS FirstOfSTATUS, First(DEDPARMS" & payp & ".AGENCY) AS FirstOfAGENCY, " & _
"First(DEDPARMS" & payp & ".TITLE) AS FirstOfTITLE, First(Right(DEDPARMS" & payp & ".title,2)) AS RepUnit, Left([DEDPARMS" & payp & ".DEDTYPE_CD],2) AS Type, " & _
"Left([DEDPARMS" & payp & ".DEDTYPE_CD],2) AS LeftType, Sum(DEDPARMS" & payp & ".NBR) AS SumOfNBR, First(DEDPARMS" & payp & ".DEDTYPE_CD) AS FirstOfDEDTYPE_CD1, " & _
"First(Right([DEDPARMS" & payp & ".DEDPlan_CD],2)) AS Tier, First(Left([DEDPARMS" & payp & ".DEDPlan_CD],2)) AS Carrier, DEDPARMS" & payp & ".DEDPLAN_CD Into Biweekly_Temp_Table " & _
"FROM DEDPARMS" & payp & " LEFT JOIN DEDETAIL" & payp & " ON (DEDPARMS" & payp & ".EMP_ID = DEDETAIL" & payp & ".EMP_ID) AND (DEDPARMS" & payp & ".DEDTYPE_CD = DEDETAIL" & payp & ".DEDTYPE_CD) " & _
"GROUP BY DEDPARMS" & payp & ".EMP_ID, DEDPARMS" & payp & ".FORMAT_NM, Left([DEDPARMS" & payp & ".DEDTYPE_CD],2), Left([DEDPARMS" & payp & ".DEDTYPE_CD],2), DEDPARMS" & payp & ".DEDPLAN_CD " & _
"HAVING (((First(DEDPARMS" & payp & ".STATUS)) Not In ('P'))) " & _
"ORDER BY DEDPARMS" & payp & ".EMP_ID, First(DEDPARMS" & payp & ".STATUS);"
DoCmd.RunSQL qryBtt
NOW THE ISSUE IS, AN ALERT MESSAGE ALWAYS POP UP SAYING SOMETHING LIKE, "You are about to paste 34590 row(s) into a new table." What would I have to do to get rid of this pop up? Eventually my users are going to use this and I don't want them to be alarmed when the message pop up.
Thank.
Joe
View 4 Replies
View Related
Mar 25, 2007
Hi.
I have three tables. Each link together by relationship. In a form, i will display the relevant data from these three tables by means of a query. However, i realised that i could not perform a delete operation as my database table still exist the particular data even if it is deleted from the query.
How do i create a delete query so that it will also automatically delete all the datas from the 3 table?
Please help.
View 1 Replies
View Related
Apr 10, 2007
I have create these two count querys that count the no of booking in January and the other no of bookings in Febuary
SELECT Count([Booking Order].[Booking No]) AS [CountOfBooking No]
FROM [Booking Order]
WHERE ((([Booking Order].[Start Date])>#12/31/2006# And ([Booking Order].[Start Date])<#2/1/2007#));
SELECT Count([Booking Order].[Booking No]) AS [CountOfBooking No]
FROM [Booking Order]
WHERE ((([Booking Order].[Start Date])>#1/31/2007# And ([Booking Order].[Start Date])<#3/1/2007#));
but i need to create a query which will bring up the two counts in the same query. This is what i have create so far but have got completely stuck and don't know where to go from here.
SELECT Count(BO1.[Booking No]) AS [Count Of Jan], Count(BO2.[Booking No]) AS [Count Of Feb]
FROM [Booking Order] AS BO1, [booking order] AS BO2
HAVING (((BO1.[Start Date])>#12/31/2006# And (BO1.[Start Date])<#2/1/2007#) AND ((BO2.[Start Date])>#1/31/2007# And (BO2.[Start Date])=#3/1/2007#));
View 5 Replies
View Related
Jul 27, 2007
Hi guys,
Can someone please tell me if there is any way to create a query that gives me ... lets say a sum of smth monthly.
I'd like to create a crosstab query to have at
rows : names
columns : months
values : sum of smth (kilometers for drivers)
Thankyou in advance
View 3 Replies
View Related
Feb 13, 2007
can I use create table query and change a fields data type?
I have a field with numbers, but when I get the table, it is set as a text field. I want it to be a number field, but I can't change the orignal table. That's why I was going to use a create table query, but I can't figure out how to change the data type from text to number.
Thanks
View 1 Replies
View Related
Feb 15, 2007
Hi all,
Is it possible to create a query that looks over two different databases for information. i.e I have two departments at work that have seperate databases and I need to be able to search for data in both of them on the same query.
Any help would be greatly appreciated.
View 1 Replies
View Related
May 13, 2014
I am trying to create a form to enter data in a table. I would like to make it pull in info from a switchboard. If the record already exists I would like it to find it and allow me to edit the info. If the record doesn't exist I would like to be able to add a new record with the data input. What is the best way to accomplish this?
View 1 Replies
View Related
Oct 31, 2013
I created a database and I manage to split it into front end and backend. now I'm going to make an EXE of the front end.My question is when I open the front end, I need the database to be empty, and them create a button that will make the user select which project he wants to open (backend).I also need to create a button that will create a new empty backend and save it as a new project.
View 7 Replies
View Related
May 20, 2005
Hi, everyone. I've been reading this forum for several days now and the information I have picked up here has been wonderful. Thanks. Unfortunately, I haven't been able to find what I'm looking for on a problem I'm having. I apologize for the long post, but I wanted to include as much information as possible. Thanks for taking the time to look at my problem.
My Background: I am a Computer Managed Maintenance System Planner for a foundry. I am responsible for the administration of all maintenance and repair records for the plant’s mobile equipment (dump trucks, fork lifts, etc.) I’ve been using Access as a report writer for several years. I have pretty good understanding of report writing and basic query writing, although I have never used pivot tables or crosstab queries, and I have only used amend/update queries a few times. I have a novice’s understanding of relational databases and I recently picked up a book on VB for MS Applications and have been devouring it. The rest of my limited VB knowledge comes from reading these forums, so my programming knowledge is still in its infancy.
What I’m Using: I am on a network that uses Windows 2K, to which I have only regular user privileges. We use an Oracle database (it is used for everything in the plant) with a third party CMMS. I use Access 2002 to do my report writing. I have a small database set up that contains links to the necessary tables in Oracle and a few tables I created to help “massage” my data for queries and reports.
My Problem: Our CMMS app is only set up to retain the current and the previous hour meter/mileage readings. I need to be able to retain all my hour/mileage readings, however, so that I can track up/down time, utilization, etc., over several different periods of time, including years.
What I Want To Be Able To Do: I want to archive my hour/cycle readings in a table so that I can reference them for later queries and reports. A couple of typical questions I want to be able to ask of this information are
1)“What was the last known hour/mileage reading for this/all equipment on <date>?”
2)“How many hours/miles have passed between <date1> and <date2>?”
What I Have Done So Far: I’ve done research into crosstab queries and pivot tables. I don’t fully understand these, but they don’t seem to be what I’m looking for. I could be wrong, though. Then I got to thinking that the easiest thing for me to do might be to make a button on my main form that would run a query to append the hour data from the db to a table I made to store the info (tblMeterArchive). The table would have a column for the Equipment# and then the append query would create a new column with today’s date as the heading and dump the data into it. The problem is I can’t figure out how to get a query to add a column to a table and stick today’s date in the heading. I’ve searched these forums and I’ve done a Google, to no avail. I have even taken time to just start going through every thread to see if this sort of thing has been discussed before. So far I haven’t found anything and my boss is getting a bit peeved at the hours I have spent working on this so far this week.
So, my question is can an append query create new columns in a table? Or, am I barking up the wrong the tree and there is a better/easier to accomplish my goals? Even a link or pointer to the proper way to search for the information I need would be much appreciated.
Thanks again for taking the time to look at my problem.
View 8 Replies
View Related
Aug 21, 2005
I am trying to create a form using VBA which will be a list of people, in a particular group, with a checkbox next to each name. The point of the form is so that the user can check those individuals who shoud not be included in a particular process. The names are the result of a query and the list will be of different lengths each time. I have tried to create a query with the first field being the person's name and the second field a checkbox, which would default to No. Then I would be able to use each name with it's checkbox to accomplish this. What I really need help with is the query, so that one of the output fields is a checkbox.
View 1 Replies
View Related
Sep 15, 2005
Hi: does anybody knows how to create auto-increment in the query?
eg.
id
1
2
3
4
...
create auto-increment in the query, Not in the table? Thanks.
View 2 Replies
View Related
Nov 14, 2005
i've created a database for city permits to be issued (which works perfectly thanx to alll your help), but now i need to be able to print out 1 permit at a time when it is issued (immediately after the data has been entered into the forms). i have 3 tables & 3 forms: contractors/owners, permtOrders (which has tabs for the different types of permits), and permitOrderDetails - each form has a button to open the next. now i need to be able to print an actual permit (like the carbon copy, landscape kind that usually tears out of a book). once the user enters the information into the forms, i need a little bit of that info from each form to be carried onto the actual printable permit. i've tried to create a query w/data from the 3 tables, but it isn't returning any results. the relationships between the tables were crreated w/the wizard & i've tried created the query a couple of different ways. help :confused:
also (i know...i'm dumb), if someone could tell me how to take a snapshot of my query it would help w/my explanation of this problem. i only know how to do this w/a report.
thanx in advance :)
View 6 Replies
View Related
Nov 15, 2005
Hi Forumers...
I have data in a table that looks like this...
SITE-ID | VALUE
Site 1 | 20
Site 1 | 21
Site 1 | 16
Site 2 | 8
Site 2 | 9
Site 2 | 12
etc...
I would like to create a query that allows me to show summary statistics for each site. eg...
SITE-ID | VALUE_MIN | VALUE_MAX | VALUE_AVG
Site 1 | 16 | 21 | 19
Site 2 | 8 | 12 | 9.667
etc...
Am relatively new to MS Access and can't work out how to create a query that does this. Any help will be appreciated.
Using MS Access 2000 (9.0.7616 SP-3) on Windows 2000
Thanks,
Chris Medlin
View 3 Replies
View Related
Apr 18, 2006
Hi,
example:
SELECT tblFalls.Guest_Name, [Account_Number], Count(tblFalls.Account_Number) AS Falls
FROM tblFalls
GROUP BY [Account_Number], [Guest_Name];
Gives me:
Smith, Joe; M698, 1
Blinke, Frank; M686, 2
Neal, Bobbie; M648, 1
I need ot to give me,
1, Smith, Joe; M698, 1
2, Blinke, Frank; M686, 2
3, Neal, Bobbie; M648, 1
each time i run the query i need to list that guests, their number of falls and assign each unique guest a number starting with 1 on up...
How?
yes, yes, i know how to do it in a report, but I need right now to be able to do it in a query alone.. anyone?
I tried:
SELECT Sum(1+), Guest_Name, Account_Number, Count(Account_Number) AS [Falls]
FROM tblFalls
GROUP BY Account_Number, Guest_Name;
=p no luck.. though it looks neat.
I also tried writing a function
Public Function GetQryNum() As Integer
If IsNull(gQryNum) Or gQryNum < 1 Then
gQryNum = 1
GetQryNum = gQryNum
Else
gQryNum = gQryNum + gQryNum
GetQryNum = gQryNum
End If
End Function
SELECT GetQryNum() AS GuestIndex, Guest_Name, Account_Number, Count(Account_Number) AS [Falls]
FROM tblFalls
GROUP BY Account_Number, Guest_Name;
But all i get is a '1' in every row.
Any ideas?
View 11 Replies
View Related
Sep 26, 2006
I am rather new to access and only have a public education, so be gentle. My problem is I have made two tables within access. The first one is names and other information (5800 accounts). The second one is names and phone numbers (2300 accounts). The names are in the system exactly the same for both tables. All the names on the second list are contained within the first list. I need to create a table or query that will match the names with the phone numbers and also show me the names that I do not have the phone numbers for. I would really like to have one massive table that contains all the information from both lists. I have made a relationship between the two tables based on the person’s name. Any ideas? Thanks.
View 4 Replies
View Related
