Detailed Record Numbers By Group In QUERIES
Oct 24, 2007
I have a table that looks like this:
caseID
date
value
I want to create a crosstab query that looks like this:
Date1 Date2 Date3 etc etc
caseID value value value
There are 500 caseIDs in my table. Each caseID has up to 5 corresponding dates/values. Therefore I only want 5 dates as the column headings....listed as 1, 2, 3, 4, 5, etc NOT the actual date (as I then end up with hundreds of column headers).
Does anyone know how to create a detailed number by each group (similar to the runningsum feature in Reports)? I know I need to somehow assign each date a "date number" but I dont' know how to.
Any ideas??
View Replies
ADVERTISEMENT
Jan 30, 2015
In my transaction table, I would like to find out the latest transaction dates of each family models. It sounds a simple Max function can get the desired result. However model number consists of the first 6 characters for the family model and the rest for the versions (variants). The Max function fails to work in this scenario. Is there any other way to group the model numbers once the 1st 6 characters are identical (the rest is ignored)?
View 3 Replies
View Related
Jun 24, 2014
I have a large table (>1M rows), and I have searched various forums for a way to add sequential numbers by Group. The query I have works, but since it's a large table, I broke it up, and did everything what starts with A-E, the F-Q, etc, and appended to a new table.
This query works, on anything that starts with the letter D or later... A-C will not work.
Basically, the source table is a list of all combinations of Part_ID and UPC_Code. I am trying to number the UPC_Code field, per Part_ID. There is an AutoNumber field (ID) that is in the table as well.
This is the SQL. Query1 is the query that is being executed, so the DCount is within this same query, if that makes sense.
Code:
SELECT tbl_upc.ID, tbl_upc.Part_ID, tbl_upc.upc_code, DCount("[Part_ID]","Query1","[Part_ID] = '" & [Part_ID] & "'")-DCount("[Part_ID]","Query1","[Part_ID] = '" & [Part_ID] & "' AND [ID] > " & [ID]) AS Seq_Num
FROM tbl_upc
GROUP BY tbl_upc.ID, tbl_upc.Part_ID, tbl_upc.upc_code
ORDER BY tbl_upc.ID;
The results of this query are that all Part_IDs that start with A through C produces a Seq_Num of 0, but any that start with a "D" or later number correctly - in other words, the first instance of a particular Part_ID is 1, then 2, and so on up to the total count of that Part_ID.
Running Access 2013.
View 2 Replies
View Related
Mar 3, 2007
I am using an access database and have range of years for customers
and want to group them together. for example:
1990
1991
1992
1993
1994
1998
and what to display them as 1990-1994, 1998.
How do I do this??? Please note that I am an Access newbie....
View 1 Replies
View Related
Mar 3, 2007
I am using an access database and have range of years for customers
and want to group them together. for example:
The table looks like this:
CustID YearID
cust1 1990
cust1 1991
cust1 1992
cust2 1990
cust2 1991
csut2 1993
And want a query that will display it like this:
CusttomerID YearGrouped
cust1 1990-1992
cust2 1990-1991, 1993
These are just examples not the actuall info... Is this even possiable???? Please note that I am an Access newbie....
View 3 Replies
View Related
Mar 25, 2005
I've got an empty pageheader, and a customer group header that repeats on every page. the customer group does not have a footer, and I have a standard agreement paragraph and a signature line in the page footer.
I want to apply a page number to either the top (group header) or bottom (page footer) of my page to reflect the PAGE OF PAGES for the group, not the overall report.
The printed report gets separated into pages for each customer and mailed to them - doesn't make sense to have running page numbers on these - I'd like the customer to be able to see that they're looking at page 2 of 4 of what I mail them, instead of page 202 of 412.
How can I accomplish this?
Sidenote: I ran across a few references to the MS Knowledgebase report samples file (RptSmp00.mdb) during my pre-post answer search. I downloaded - I browsed - I learned a few new tricks. Good stuff.
Slaughter
slaughter at missouri dot edu
View 2 Replies
View Related
Aug 12, 2014
I'm having multiple problems with my database like things such as -
i'm currently working on the Query 2 - On the Phone database (ignore Query 1) and i want to search for multiple plot numbers preferably in one parameter prompt with a comma to seperate numbers. (this could be a multitude of numbers so i would like to be able to input as many as needed). Also when i do search on this query since the Criteria is a 'Between' Value i would expect everything between the 2 numbers input to show up - but a lot of numbers out of the range show up too - why is this? (The Numbers are like "69 to 136" and they will show up - but 1-69 and 136-170 would too
I would also like to implement the search results from Query 2 into the Form i currently have made but it just opens up a access table when the search is made?
i cannot link my database as it is too big for the server - But here are the Criteria for Query 2:
Plot No - (criteria = Between [Enter First Plot No:] And [Enter Last Plot No:])
Site - (criteria = Like "*" & [Enter Site:] & "*")
Product - (criteria = Like "*" & [Enter Product:] & "*"
The Query is the one im most concerned about , i can live without a form.
View 14 Replies
View Related
Nov 7, 2005
Folks,
i have a report that i designed to capture some detailed information and my users say its too detailed and they cant use it that way,so they want it scaled down to that very brief format as in the attached pdf file.I conquer with them but i cant seem get beyond what i did as every table seems to be a sub table of the another thus the cause of grouping and sectioning.
Has any one done something like this before!!, or are there any ideas,tricks, examples i would look at as to meet this requirement!!!
Evey Idea is highly appreciated.
Thanks in Advance
Kea
View 3 Replies
View Related
Mar 7, 2006
I have a Movie db which consists of one table with columns such as Actors, Genre, Rating, etc. I currently have numerous buttons which are used to search each specific data type. For example, I can search Genre, or Year, but not both. Is there a way that I can create a form, where various items are selected and from there, a search is conducted based on the various selections? I also want to be sure that if a single field is blank, that it just ignores the field and doesn't search for "blank" fields.
Thanks so much.
View 1 Replies
View Related
Dec 1, 2005
I am creating a Sales History Report. It displays products sold by customer by Sales Rep. The groupings are Sales Rep, Customer, and Product. I am hiding the detail section which contains invoice line item records. So the report shows the product subtotal by Company. The problem I have is I need to see the product subtotals in the Sales Rep and Report Total sections also. So the report would look something like this:
Sales Rep A
Customer A
Product A
Product B
Product C
Customer B
Product A
Product B
Product C
Sales Rep A Total
Product A
Product B
Product C
Sales Rep B
Customer R
Product A
Product B
Product C
Customer S
Product A
Product B
Product C
Sales Rep B Total
Product A
Product B
Product C
Report Total
Product A
Product B
Product C
Is there a way to accomplish this without doing sub-reports?
View 3 Replies
View Related
Dec 9, 2013
I want to automate the distribution of individuals Vacation Balance and Usage via email. The data is being pulled from 2 tables related by an Employee ID#. tbl-Employees provides the Header Info for each employee and tbl-VacLog provides the detailed Usage data.
I want the output to appear as follows in the body of the email:
Name Start Date Vac. Bal TotVacToEOY Personal Bal.
John Doe 1/1/99 120 160 8
Usage Date Hours Reason Code
1/1/13 8 V
2/15/13 12 V
3/6/13 8 V
I've got a handle on creating the email and sending but where I'm having trouble is in making the link between the two tables with the Employee ID# and printing the corresponding detail data with Parent Record.
Code below...
Option Compare Database
Sub SendMails()
Dim DB As Database
Dim RecordSetA As DAO.Recordset
Dim RecordSetB As DAO.Recordset
Dim TotalRecordsA As Integer
[Code] .....
View 1 Replies
View Related
Dec 22, 2014
I have a combo named cbogroup. I have a tblGroup with several records (active, non-active, nursery, etc.). One of the records is *ALL*. Using the CboGroup the user can pick any of the records. Howeverr, if they pick the *ALL* record, I want the query to pull up animalID based on all records in the TblGroup. If another record is picked (i.e. nursery), then the query will pull up only animalIDs that are in the 'Nursery'.Can I put a (iff then) in a query in order to differentiate a query based on all group records or a query based on only one record?
View 14 Replies
View Related
Apr 10, 2012
Access 2000 Win 7. I want to have some numbers move from the record I am using to a new record when I move to the next record when it opens?
View 1 Replies
View Related
Mar 22, 2005
I am wondering if it is possible to code something that changes the record number of a selected record. I have a form that lists the schedule of the company. I want to be able to move the position of the records around so it can be changed. The form is based on a query and is sorted by date and sequence number of that date. For example, I would want to be able to move record number 3 on the form to record 4 and vice versa. Thanks :)
View 1 Replies
View Related
Jul 17, 2012
My data is structuredin the following format:
ITEM......................PRICE
X..............................$10
X..............................$10
Y------------------------$25
Y------------------------$25
I would like to calculate each item's price only once in the Access report footer. However, when the SUM formula is entered I get the cumulative price for each item (i.e. $70 as opposed to $35).
Is there any way I could just show the price of the first item of the group only? I'm thinking of creating a new field in a query and adding 1s and 0s, but don't know exactly how.
View 3 Replies
View Related
Jun 6, 2005
I would like to hide the record number scrolling feature in my forms. How is this done? I have searched through the properties of the form but am missing something I suppose.
Thanks, Joe
View 7 Replies
View Related
Dec 21, 2006
Hi,
I thank everyone in advance if they can help me with this.
What i would like to do is display the record number on my form in between the record navigation buttons that i have allready added.
I can hear poeple now saying, " why dont i display the navigation buttons in the form properties?" and the reason i do not wish to do this is because i do not want poeple to be able to add a new record!
so, if someone can help me remove the "New Record" button from the standard navigation then that would be excelent.... but if not then how can i replicate the record number into my form?
Thank you all again for your help with this
Bev
View 2 Replies
View Related
Jul 21, 2013
I have written a large number of queries to gather data for a quarterly monitoring form. How do I replace the dates to update them for next quarter.For example I have written a query which counts the number of new members who started in the period 01/04/2013 to 31/07/2013. How can I change this (and the other 200 queries) without opening them all individually and manually altering it. Is there an Access equivalent of Word's Find and Replace?By the way I am using Access 2000.
View 11 Replies
View Related
Jun 10, 2005
FYI: I'm new to Access, but have some knowledge of SQL and VBA. I'm using Access 2000.
I've looked through past posts to no avail.
I have a form which allows users to supply one or more criteria to subset a
recordset. The "Execute" button on the form kicks off VBA which builds and
runs a SQL statement using the selections made in the form.
The recordset that the SQL runs against can have multiple rows for each
ProjectID -- based on a combination of a couple of fields. For instance, the
following is possible:
ProjectID---StartDate---Employee---ProjectCategory---... <other fields>
1-----------01/01/05---Herman-----App Dvlpmt
1-----------01/01/05---Hortence---Consulting
1-----------01/01/05---Herman-----Consulting
1-----------01/01/05---Hortence----App Dvlpmt
If the user wants to select ProjectIDs where Employee="Herman" -- without
making a selection on ProjectCategory -- I want only one of the two
"Herman" rows above to be returned. And I don't really care which one.
(Similarly if the selection is only on ProjectCategory)
The problem is that, using the code below on the example above, both
"Herman" rows are returned. I've tried numerous approaches -- this being
the most recent. The SQL statement is being built as I expect, and it's executing.
It's just not giving the results I want/expect. Also, I've hardcoded selections
into a stored query similar to the one below, and it works. Any ideas on what am I doing wrong?
Private Sub cmdExecuteQuery_Click()
Dim strSQL As String
strSQL = "SELECT ProjectID, first(ProjectName), " & _
"first(StartDate), first(EndDate), first(ProjectActive), " & _
"first(Sector1), first(Sector2), first(Sector3), first(ClientShortName), " & _
"first(Employee), first(ProjectCategory) " & _
"FROM qryProjectsForReport WHERE (ProjectActive = "
Select Case optStatus
Case 1
strSQL = strSQL & "True) "
Case 2
strSQL = strSQL & "False) "
Case 3
strSQL = strSQL & "True or ProjectActive = false) "
End Select
If Len(cmbCategory) Then strSQL = strSQL & " AND ProjectCategory =
" & cmbCategory
If Len(cmbMember) Then strSQL = strSQL & " AND Employee = " & cmbEmp
If Len(dtStartDate) Then strSQL = strSQL & " AND StartDate >= #"
& dtStartDate & "#"
If Len(dtEndDate) Then strSQL = strSQL & " AND EndDate <= #" &
dtEndDate & "#"
If Len(cmbClient) Then strSQL = strSQL & " AND ClientID= " & cmbClient
If Len(cmbSector) Then
Select Case cmbSector
Case 1
strSQL = strSQL & " AND Sector1 = True"
Case 2
strSQL = strSQL & " AND Sector2 = True"
Case 3
strSQL = strSQL & " AND Sector3 = True"
End Select
End If
strSQL = strSQL & " GROUP BY ProjectID ORDER BY ProjectID;"
MsgBox strSQL
OpenReport strSQL, chkDatasheet
End Sub
Thanks in advance!
DM1968
View 2 Replies
View Related
Apr 30, 2008
hi.
first of all, i am glad to join this lovely forum. i've read some threads, people here are very very helpful..
now the question (please bear with me if it's too basic, i am relatively new to Access):
i've created a 'select' query which does everything for me, except one thing - it doesn't mark one case for a certain group of records. here is the excerpt of how it looks, the last column being the one i can't figure out how to do:
Prop Date Category ID ID_unique
x1 2/3/08 001 2351 1
x1 2/5/08 001 2351 0
x1 2/6/08 001 2351 0
x1 2/9/08 001 2351 0
x1 2/3/08 001 2352 1
x1 2/4/08 001 2352 0
x1 2/3/08 001 2354 1
x1 2/4/08 001 2354 0
x1 2/8/08 001 2354 0
so for each group of records with the same ID, i need to mark only one of them.
is it possible?
thanks.. l
View 12 Replies
View Related
Oct 5, 2004
In have a table which contains customer address details.
I simply want to display a 2-column list. the first column
to display a disnct list of city's from the main table.
the corresponding column must give the no. of records that
contain the entry in the first column.
eg if the main table had 5 customers that lived in london
and 6 in Maidstone then
London 5
Maidstone 6
etc
View 1 Replies
View Related
Aug 30, 2005
I have a database in Access 2000 that contains multiple entries for each individual - in this case they are 'visits'. I wish to create a query that shows the first and last visit only on any particular day for each individual.
Can anyone help me?
View 7 Replies
View Related
Aug 19, 2005
Hi,
How can I make Records numbers in Continous form?
For Example, in Continous Form I have 10 records, the numbers are from 1 to 10. Then I make filter and see only 4 records. I need to see from 1 to 4.
Any ideas?
Thank You in advance.
View 1 Replies
View Related
Mar 24, 2006
Hi,
I am trying to display the records (counts) on my Invoice form.
I tried to put one text box and typed as follows:
=Count-[n]
This kind of text box we normaly use to count records on Reports and it counts well. But this did not support me on form.
I need, it should display records from 1,2,3 and so on upto the last record entered for that invoice.. Each new invoice should start from 1 to the last record number.
Any idea ?
With kind regards,
Ashfaque
View 4 Replies
View Related
Nov 14, 2004
Hello
I 've created a report that uses a query to show some specific records of a table. I'd like to use a text box that has a nuber for each filtered record shown.For example:
Number Name Surname Age
1 Jim Powell 27
2 John Doe 30
etc
Thank in advance
View 3 Replies
View Related
Mar 8, 2008
Hi Guys, sorry to bother you but I'm really stumped. I've looked through some similar sounding old posts on here but they don't seem to answer my problem...
I've got a report, which is grouped, in the footer of each group a SUM label should add the contents of the pricce column, however strangely it only adds the first record of each group as a running total. I've tried both the "Over all" and "Over group" attributes of the running sum property, but they make no difference.
Any help would really save my day. Cheers!
View 11 Replies
View Related
