Is there a way of finding out who is turning18 years old in the next 6 months on a set day.
Specifically, I want to minus a date of birth from todays date (DateDiff?) and then for it to show those people who will be turning 18 in the next 6 months.
I've tried a range of Datediffs but I can't seem to get what I need.
I've created an access chart to show total sales ordered by customers.
I'm using a form with 5 comboboxes to select 5 particular customers from a customer table and pass this information into a query.
This query is then used on a report to create the graph.
Finally there is a button on my form that prints this report.
My problem is that the chart displays the customers in alphabetical order and I would like to order the customers numerically based on total sales value.
If I change my query so that the total sales order by is ascending then when I run the query from the report I am asked to enter a parameter value for the total sales field. Entering nothing and pressing ok simply returns the same graph I would have got had I not changed the order option at all.
*The customer filed in the report is set to group by and my totals field is set to sum - but I need both of these set to produce the graph.
I have 3 cross tab queries that are completely identical with the exception of the field that they pivot. Each field is searching for the same values just in different columns, with the end goal being to get the sum of the values for each pivoted column. I'm wondering rather than having 3 almost identical queries is there a way to use a crosstab to sum the values from each of the three fields rather that having 3 queries which then have to be aggregated in a fourth?
QUERY1
Code:
TRANSFORM
IIf(Count(PT_LEVEL.UNIT) Is Null,0,Count(PT_LEVEL.UNIT)) AS CountOfUNIT
SELECT
PT_LEVEL.INF_YEAR,
PT_LEVEL.INF_MONTH,
PT_LEVEL.UNIT
[code]...
I can't seem to figure out the proper syntax for the FindFirst method. I am using several variations of this effort:
Dim dbs As dao.Database
Dim rst As dao.Recordset
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("tblInvoice", dbOpenDynaset)
rst.FindFirst "rst!ID = frmInvoice!txtID"
I get an error message that says that Access doesn't recognize rst.ID as a valid field. But, it most certainly is. I tried substituting tblInvoice but got the same error.
I am using MS Access 2007.
I would like to display a report based on the table called "expenses", filtered by:
- "from" (datefrom field) and "to" (dateto field) date on "payment date" field;
- multiple criteria on same field called "payment method" (I would like to include only payment methods "check" and "credit card", but not the other payment methods in the field, such as "cash", "transfer", etc.)
For that end I made a query based on the table "expenses", and in the "payment date", in the criteria field, I entered:
between [form]![formname]![datefrom] and [form]![formname]![dateto]
This works fine so far, however when I attempt to add multiple criteria on the "payment method" field, it does not filter accurately any longer. In the same row of the criteria field where I completed the date criteria, I enter "check". In the next row, same field, I enter "credit card".
Since it doesn't work, I tried putting both arguments in the same line as the date criteria (always in the payment method field) as: "check" and "credit card" but still does not work (now it filters the payment method correctly, but the dates filter appear as if I have never completed them).
Hallo!
I'd like to make querie to show all changes made in database (one table) in some period of past time.
Please help me!
Robi
I would like to show all linked components (once) in a query.
With inner joins (option 2 when double clicking on a relationship line), it shows all linked components but with duplicates.
When selecting option 1 (only include rows where the joined field from both tables are equal) I only get 1 row due to the fact that not all components have values in all columns.
Therefore, How to create a query that shows all components that have one or more links to other parts. These links have be checked 3 tables.
I attached a overview of the tables involved.
Is it possible to only show a percentage, like 5% of records using a query? Ideally i would like my query to show a random 5% sample from a table, that would be great. Is this sort of query possible?
View 3 Replies View RelatedI have two tables. One with a persons name and ID, DOB, GP etc (TblName) and one with the persons ID and medication they take (TblMedication). I have a query that runs a report. The header is made up of the first table (TblName) and the body of the second table (TblMedication). All works fine but it shows all records from the first table (TblName) regardless of whether they have medication or not so the body shows up blank. I want to run the query with both tables information but only show the persons header if they have medication.
View 14 Replies View RelatedHow can I have this query to show no duplicate records.
I would like to only see the first record only of each stock code.
I've used this parameter back in Access 2.0 where I ask for I put [What location?] as the parameter and users can put 'North' or hit enter to get all locations.
View 3 Replies View RelatedCurrently I have a query, QRY_Test which contains a load of records under the fields "Gift_ID" and "Person_ID". I have another query, QRY_PersonList in which I would like to show all of the "Person_ID"s except the ones returned by QRY_Test.
View 3 Replies View RelatedI have 4 columsn, numbers 1 is ModelID, 2 is FitPerSheet, 3s NeedCutOut and 4 is Time: [NeedCutOut]/[FitperSheet]
Thing is, I only enter some numbers into NeedCutOut, and I dont want to see any of the Models that I dont have a NeedCutOut number for.
here is the SQL code just in case. Never know.
Code:
SELECT tblPerSheetTime.ModelID,
tblPerSheetTime.FitPerSheet, tblPerSheetTime.NeedCutOut, [NeedCutOut]/[FitperSheet] AS [Time], *
FROM tblPerSheetTime;
I'm working on a published sharepoint web access database, writing a criteria expression in data source of a combo box.The field I'm setting criteria is called SharePointAuthor.
I wrote this critea:
IIf(CurrentWebUser(1)="John Doe","*",CurrentWebUser(1))
Trying to show all records when currentwebuser is John Doe, otherwise, show only records created by currentwebuser.Funny thing is that it turns out "John Doe" couldn't see any records, while other users can see their own records as expected.
How can i make a query which shows the most used data in a field, i saw how i can show the biggest or smallest but i didn't see how many times is each data used in a field.
My field is for years, and i wanna see each year and how many times it is used.
Is this possible to do?
I have a table and there are so called duplicate records (not entirely duplicate because there is field with the date of creation and other differences in a few fields, but from my point of view they are duplicates.). I d like to make a query which shows me only the newest record in case of duplicity.
View 3 Replies View RelatedI have a table with 5 fields (F1, F2, F3, F4, F5).
F1 has 3 values (v1,v2,v3) and the other fields have different entries depending on F1.
-----------------------------------------------
F1 F2 F3 F4 F5
-----------------------------------------------
v1 f31
v2 f21 f41
v3 f41 f51
Is there any way to filter F1 to show only the fields which have a value?
(if I choose v2 to have shown only fields F2 (with f21) and F4 with f41
I have a table that includes a date/time field. I want to query that table and show all but the TOP N records. Since the number of records will always be changing I cant do it using the BOTTOM N records.
View 2 Replies View Relatedhow to have our query show ONLY the records with NO DATA in the company field?
View 12 Replies View RelatedI have a query which is supposed to show the revenue from each advertising source.
PLease see attached in zip file (cant post pics b/c I have less than 10 posts)
As you can see it has revenue amounts for the same source of advertising:
Radio has two amounts, INternet has two etc..
What can I do to only have each form of advertising show up once?
and the revenue amounts summed up for each advertising medium.
After hours of playing around i finally have my IIF statement working the proplem is now is that when the Value is null it gives a blank value on my Report. How do i get it to show the value of 0 when it is Null. can i use an isNull withen my IIf statement maybe nested what is best practice?
View 6 Replies View RelatedI have a database about certain accounting datas from week by week and it's growing. I should make a Crosstab query for see the amounts weekly, it's will be exported to an excel workbook, wich have macro's(this is the problem, because the exported field will be bigger week by week). The difficulty of this query is the future weeks. I want to see all of the weeks in the columns. I made a table wich contains the weeks(Hetek_1.CW) wich I want to see, and the source is also contains the items accounting weeks.
Code:
TRANSFORM Sum(CWall_hetekkel.[Knyv# ssz# kltsg]) AS [SumOfKnyv# ssz# kltsg]
SELECT CWall_hetekkel.[Ktgh# kdja], Oka.Oka
FROM Oka RIGHT JOIN CWall_hetekkel ON Oka.Oka = CWall_hetekkel.Oka
WHERE (((CWall_hetekkel.[Ktgh# kdja])=1250 Or (CWall_hetekkel.[Ktgh# kdja])=1251 Or (CWall_hetekkel.[Ktgh# kdja])=1252 Or (CWall_hetekkel.[Ktgh# kdja])=1253))
GROUP BY CWall_hetekkel.[Ktgh# kdja], Oka.Oka
PIVOT CWall_hetekkel.CW;
I am trying to build a query to show the total sum of hours a staff member has done in a certain department and the amount of sleep ins they have done. Each staff member has a unique ID. In the query I can get all the information but it shows on two lines dependent on department. I want this to show on a single line. Each employee will never have more than two departments. The query is called "QryForForum".
View 2 Replies View RelatedI created this Vehicle Maintenance and Parts database. In the 'Vehicle Parts Maintenance' button in the frmMenu form, you are supposed to choose the vehicle on the left side then with the drop down on the top right pick what maintenance type you want. Right now when you pick 250 hours it shows all parts attached to the vehicles not only the parts that are supposed to be changed at that maintenance cycle.
The whole database is attached.
I have a query that selects a group of customers and order dates.
I want the query to only show the most recent date for each customer.
How would i achieve this?
I have a table in this manner:
Customer | Debit Amount | Credit Amount | Date of Transaction
My goal is to have the customer's name | (Sum of Debit amount MINUS Sum of Credit Amount) and it will only show if it is past a certain due date.