Hi,
Im trying to run a query, field name is [Value]. I have yes or no boxes in this field, I set the sum to count and under criteria i have put yes, no, -1, and 0 and they will not correctly add up. When i try yes it counts all of my records. Any help would be great. Thanks.
:mad: I have created a database to hold information on non conformances we have with our suppliers, I am really stuck, what I am looking for is to put a field into a table which counts issues with the different suppliers, if anyone can help it would be much appreciated! Thanks
View 1 Replies View RelatedOk so I am attempting to create a simple count query and have had some issues.
The table looks like so:
http://img.photobucket.com/albums/v292/Listerman/table.jpg
and I need the query to display the count for user: Nick, no and yes.
So the result would be:
Nick - Yes - 2
Nick - No - 5
and so forth for each user.
Is this possible in one query?
I have a table tblBookings.
In this table it has a bookingID, CustomerID and some other none relevant details.
The CustomerID comes from table tblCustomer. i.e a customerID must exist in the customer table to be allowed in the bookings table tblBookings
A customer can exist in tblCustomer without existing in the booking table.
I am trying to write a query that will list each and every customer ID in the tblCustomer and count the number of bookings that that customer has (even if it is zero).
I have a query that will count the bookings if they exist in the booking table and display the number of times that a customer appears in the bookings table.
SELECT tblBookings.CustomerID, Count(tblBookings.CustomerID) AS NoOfBookings
FROM tblBookings
GROUP BY tblBookings.CustomerID;
How do I create a query that will do this but list all customers even if they don't exist in the bookings table (but obviously occur in the customers table)
I am trying to create a similar query where all bookings per hotel are listed even if no bookings are made for that hotel. I am guessing the answer is the same as above.
The Ritz. Bookings 0
The Hilton. Bookings 3
The Carlton. Bookings 0
The Lowry. Bookings 2
For every hotel.
That kind of thing.
If you need more information please shout.
I have a table that has 5M+ accounting line entries. Below is an example of one accounting journal in the table.
BUSN_UNIT_IJRNL_DJRNL_ICNCY_CMONY_A
CB0014/07/20140002888269323AUD16797
CB0014/07/20140002888269323AUD-16797
CB0017/07/20140002888269323AUD16797
CB0017/07/20140002888269323AUD-16797
The journal ID above was an accounting entry, debit $16,797 and credit $-16,797. because it was entered as a reversing journal in the system, the table has captured the Journal ID with 2 dates. For my purpose i only want the one date (MIN) date, the total amount of the journal (either the debit or credit amount 16,797) and the total number of lines the journal ID has so in this instance I want the count to be 2 and not 4.
Right now this is what i get
BUSN_UNIT_I JRNL_I CNCY_C SumOfMONY_A CountOfJRNL_I MinOfJRNL_D
CB001 0002888269 AUD 0 4 4/07/2014
This is the output i would like
BUSN_UNIT_I JRNL_I CNCY_C SumofMONY_A CountofJRNL_I MinOfJRNL_D
CB0010002888269323 AUD16797 2 4/07/2014
Im thinking with the total sum because theres debits and credits is there a way to do the absolute value of the journal MONY_A then divide by 2?
current SQL
SELECT [One Year Data Lines].JRNL_I, [One Year Data Lines].CNCY_C, Count([One Year Data Lines].JRNL_I) AS CountOfJRNL_I, Min([One Year Data Lines].JRNL_D) AS MinOfJRNL_D, [One Year Data Lines].BUSN_UNIT_I, Sum([One Year Data Lines].MONY_A) AS SumOfMONY_A
FROM [One Year Data Lines]
GROUP BY [One Year Data Lines].JRNL_I, [One Year Data Lines].CNCY_C, [One Year Data Lines].BUSN_UNIT_I
HAVING ((([One Year Data Lines].JRNL_I)="0002888269") AND (([One Year Data Lines].CNCY_C)="aud"));
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?
Hi,
Just spent the past hour in here trying to nut this one out, but not sure I've found something quite the same...though I know the answer will be painfully simple.
I have a customer table and a product table, and a query that groups customer first and last names along with a count of products per customer e.g. 1,1,3,2,3,4,2,1 indicates customer A buys qty 1 of product z, customer B buys qty 1 of product x, cust C buys qty 3 of product y and so on.
All I need to do now is do something to also output the total number of products. ie as per example above, 1+1+3+2+3+4+2+1 to get 17.
Can I do a count of the count or do I do some sort of sum of the count results?
I've tried everything I'm capable of as a newbie, and I'm not having any progress.
Any help appreciated.
Hi all,
Do someone have a way to do a count function in a Reports to let the report showing the details at the same time do the accumulating of the number??
I had try so many way but it not work~~!!! Pls i need someone help cause i stuck my report there without the accumulating of the number, Thanks.
Regard,
alex
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.
:-)
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
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
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.
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:
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 ?
: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
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 :(
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)
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 !!
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
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.
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 Relatedthis 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
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
: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 RelatedI have a very basic form (frmHalfTab) based on a basic table (tblHalfTab2). The table is uploaded periodically from excel, overwriting the original table. I'm trying to determine the best way to accomplish the following:
I need a query, Dlookup, SQL statement, or something, that will look at the contents of one field (MBRDrug) and populate the following field (MBRDrugAlt) based on what's in the first field. For example, if MBRDrug field has "Pravachol 10MG" in it, I need it to fill in MBRDrugAlt with "Pravachol 20MG". I am hoping to set this up in the form's OnOpen event to occur automatically with no direct user input, as this is what my boss is asking for. Any suggestions on how to proceed? Also, in my failed attempts, I have the original drug and it's suggested alternate in it's own table, tblHalfTab1, if that helps with any suggested solutions. Help?
This has racked my brain. Problem=
The "simple problem" is to create a 2nd subform that alerts the user that a vendor order has already been generated and show a listing of what forms they are. This is determined by field VendorOrderID. So I thought a little something like this would work.
If BidOrdersSubDetail.Form.VenID = VendorOrdersub.Form.VendorOrderID Then
BidOrdersSubDetail.Form.AllowAdditions = False
VendorOrdersub.Form.Visible = True
Else
If BidOrdersSubDetail.Form.VenID = VendorOrdersub.Form.VendorOrderID Then
BidOrdersSubDetail.Form.AllowAdditions = True
VendorOrdersub.Form.Visible = False
End If
End If
:( then came Error 2427 :eek: expression has no value.
Now i have lost the last of my hair to pull and rewrote the code several ways. It will not let me assign a value of 0, I cannot figure the propper use of IIF, true, false, :confused:
any help, the vendor id is not generated untill after the order is created. They can review an order, which is the reason to block additions to the main sub form, and to notify the user that it is complete and what vendors were used. There for there is no value to vendorOrdersId for a new one which is the only identifier I have.