Group Numbers

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 Replies


ADVERTISEMENT

Group NUmbers

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

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 3 Replies View Related

Reset Page Numbers For Each Report Group

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

Queries :: Way To Group Model Numbers Once 1st 6 Characters Are Identical

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

Queries :: DCount To Add Sequential Numbers By Group Only Works In Some Instances

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

Queries :: Search For Multiple Plot Numbers Preferably In One Parameter Prompt With Comma To Separate Numbers

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

Reports :: Sum Group Items By Specific Item And Hide Details Of Group Summing

May 29, 2015

Despite Google I can't seem to figure this out.

I have some data in a format similar to:

Name / Style / description / speed / distance
john / driver / careful / 80 / 5500
mary / driver / careful / 70 / 7000
pat / racer / reckless / 100 / 6000
anne / driver / careful / 75 / 1000
peter / racer / reckless / 110 / 6500
don / snail / slow / 60 / 6000

I want my report to total by style, without details and to look like:

driver careful 13500
racer reckless 12500
snail slow 6000

How do you get a report to sum the group items by a specific item and to hide the details of that group summing?

View 2 Replies View Related

Reports :: Control Group Expression For Group In Report?

Mar 28, 2013

Is there a way to have an expression in the control source of a text box in a report, that re-starts or is exclusive for every group within the report?

View 5 Replies View Related

Numbers Stored As Text Convert To Numbers?

Jan 10, 2007

Ok so in excel I have some numbers that are stored as text. The reason being that they are zip codes and some begin with 0 and excel doesn't want numbers to start with 0....so when I import these into an access field that has an input mask for zip codes...will it convert these correctly since the field is a text with input mask?

View 1 Replies View Related

Modules & VBA :: Automate Line Numbers And PO Numbers

Aug 24, 2014

I have 2 fields that I would like to automate if possible

One field is called "p/o number" and another field called "line no"

These fields are part of an ordering database

Let say I have 200 items to purchase form 10 suppliers

And form example 20 items from each supplier

What I do at present is put the order number on each line item and the line number

example

p/o number line no

1 1
1 2
1 3

2 1
2 2
2 3
2 4

What I want to do is just put the first po number in the required line . Put the first line number in i.e. "1" and the macro will complete all the p/o numbers and line numbers for me as per the ones marked in red.

Example

1 1
2 2
3 3

2 1
2 2
2 3

View 5 Replies View Related

Forms :: Option Group - Warn User When Neither Of 2 In Option Group Not Ticked?

Jul 25, 2013

I stumbled upon the Option Group function just yesterday and, happy as a clam, I created a group with 2 options in radio button style. I assigned the values to a field called Registration_Type as the 2 options are "Confirmed Registrants" and "Prospective Attendees".

[Great. That part works well. When I look at the table, a 1 or a 2 is in that field so it's great to know how to control accidental ticking of radio buttons (previous 450 records or so didn't have this option group functionality so one might easily tick one of the buttons. So one part of controlling option group I know I can handle via the table itself for now.]

The challenge is how to ensure the user always ticks one or the other ... I went back to the main table and tested the 'required entry' option for the Registration_Type field but forcing an action like this is not ideal in my mind. The usual error message vagueness for the average user is no good and I don't want to limit the user so much.

Is there a way to simply have a popup come up warning that neither radio button was ticked? Perhaps something linked to the form - i.e., maybe "after update"?? I only learned about attaching code to before and after update on controls a couple of days ago, so not sure if this would be best approach.

Just something to let the user know that nothing has been ticked in the option group as that controls in which of 2 reports the data will show up in so any record not ticked might mean a registrant being left out, which would be rather disastrous <g>.

View 1 Replies View Related

Counting Frequency Of Numbers And Comparing To Frequency Of Another Set Of Numbers In Same Row

Oct 28, 2014

I have a table with fields like this one but the weeks go all the way up to 52. What I am trying to do is count the number of consecutive zeros and if it is more than five, count how many of the following fields have a number in them and if that number is less than the number of zeros preceding it identify that person.

For example Joe would be identified below because he had 6 consecutive zeros and then he had 5 weeks of numbers immediately following the string of zeros. Bob would not be identified because he had 5 consecutive zeros and then 5 sets of numbers immediately following the string of zeros so the zero frequency isnt higher than the number frequency immediately following.

ID
Name
Date of Hire
1
2
3
4
5

[code]...

View 5 Replies View Related

Between Numbers

Apr 20, 2006

I am trying to write a function along the lines of the following:

If Me.Text1 > 0 but < 2 Then
Something happens

Clearly the top line isnt correct, so how do I write a function like that? Thanks

View 2 Replies View Related

Exactly 8 Numbers

Dec 11, 2007

My brain has locked up, I just added a field to an existing db and I need to to hold exactly 8 intergers / numbers; typically the first few numbers are 0's, but the 0 do not display; still using 97, what is the field size/format/ going to be?

Thank you in advance

View 1 Replies View Related

Even And Odd Numbers

May 16, 2006

Not sure if this is easy or not, I have searched the forum but am not finding what I am looking for. I need to see if there is a way, in a query to extract data specific to either even or odd numbers. So if I have a field on a table with data like this:A01AA02AA03AA04AA05AA06AI want to pull just the even or odd numbered values.Any ideas??

View 5 Replies View Related

Numbers Only

Jun 15, 2007

Is there a way to write a query where it will only capture numbers? For example, if I have the following:

"Testing transactions 11100202020 in the following order"

What I would like to do is capture the numbers that is in the middle of the sentence. I have tried using the len, left, mid, right functions in my expressions, but the sentence length may change from time to time. Is ther a way to tell access to only capture the numbers?

Thanks in advance
mlr0911

View 14 Replies View Related

Numbers And %

Sep 2, 2004

The database that I will be referring to in this querstion ws developed to help track interviews at a local helth care facility (dates, times, location, ethnicity, age, state etc.)

I am trying to develop a few queries to pull certain information out this database. Specifically I am trying to pull out information in NUMBER or % format. Currently I can only figure out how to pull out the information that is currently listed. For example: We have had 57 interviews come into the door this month. All of these inteviews are going to different areas and they all are of different ethnic origin and all have an age difference. When I go to query how many total interviews we have had, I am getting names and not numbers. When I try to query how many different ethnicities have been interviewed, I still get names attached to what ethnicity they are.....so on and so forth. What I would like to do is turn these names into statistics or numbers. Is there any way to query this information and get it to come out in number format? The following is an example of different metrics that I am trying to get from this database.How many total out of state interviews per monthThe number of interviews per state per monthHow many african americans interviews peer per month, how many pacific islander interviewed per month, how many hispanic interviews per month........etcHow many internal candidates iterviewed per month/How many external candidates interviewed per month.The information entered into my fields are not entered in a numercial format. They are intered as names, places, dates, times, etc....My question is can I query this information in such a way that I can generate a report that would allow me to view it in numerical formatAny help will be GREATLY appreciated. Thank you soooo much!!!!!

View 3 Replies View Related

Problem With Numbers

May 4, 2005

Hello,I am new here and new to Access.
I have started to build a database that will be for storing the results of horse races.
However my problem arises when inputting the weight a horse carries.

The weight can be input as, for example, 11.12 or 11.00 or 10.10 however whilst I have no problem with 11.12 the other two appear as 11 and 10.1 is there any way round this.

Thanks for any Help
Treggy

View 3 Replies View Related

Generating Numbers

May 5, 2005

hi,

I have a table that has results from races, eg.

Race No, Racer ID, Time taken.

i want to have a position field that will say which position the runner finished in. This is complecated as there are many different races.

At the moment the best thing ive been able to do is set up a query with a parameter on the race no. then the time taken is put in order and i have to manually put in the positions.

Manual is bad!

does any one have any idea about how i could make this automatic.

Thanx,
MAtt

View 1 Replies View Related

Trim Numbers

Jul 6, 2005

Tried to search for this, maybe I am searching the wrong term.

Anyways.. I have a table linked from a large mainframe, and what I am trying to do is trim any numbers that are 1 million and over, and still keep it a number.

Currently I have this:

VEHICLE_KMS2: IIf([VEHICLE_KMS]>999999,Left([VEHICLE_KMS],6),[VEHICLE_KMS])

What I need is to have it remain as a number, seems that the left function turns it into a string value.

Thanks

View 7 Replies View Related

Renumber ID Numbers

Jul 7, 2005

I have 8 databases (A97) (don't even ask me why) they are identical.
I want to combine all the tables from them into one big table.
There are two tables 1 called Transactions and it has a transactionID (number) (parent of DetailID) and the other table is called Detail and it has a DetailID (number) (Child of TransactionID).
The problem is in all 8 databases the transactionID is an autonumber starting at 1 so I will have duplicate TransactionID & DetailID numbers. Each of the databases comes from one of eigh different "Regions" of our company and we assign Region numbers I can use as a prefix to the transID/detailID.
I tried to put a "25" (region#) in front of the one of the TransID# by using the format property in the table design and that seems to act as some sort of gost because while you can see it in datasheet view when you click on the cell the "25" disapears and all you can see is transID#1...#2....and it also gets dropped on paste append to the new combined table.
The question: is there an easy way to put a region number prefix on transID# so I can merge all 8 transaction tables together into one combined table?

Thank you

View 3 Replies View Related

Removing 3 Numbers

Aug 18, 2005

I am looking to create a make table query to eliminate some portions of numbers from a field.

i have 23899999999

I need to parse 899999999 into a new field.
Is there a formular for that. Do I use a LEN formula.

View 2 Replies View Related

Random Numbers

Aug 30, 2005

Hi,

Can you please help me out--give me some guidance-if you have knowledge about random numbers. Say, for instance, I have 2,000 records and I want to randomly create a final list with 800 of these records, how do I go about it? Thanks.

View 2 Replies View Related

Numbers To Words

Jul 7, 2006

Dear all,


If some have the code for converting amount to words in rupees, please help me...

Thanks in advance

Thanks:(

View 1 Replies View Related

Numbers To Words

Jul 7, 2006

Dear all,


If some have the code for converting amount to words in rupees, please help me...

Thanks in advance

Thanks:(

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved