# Assigning A Range Of Numbers To A Category

Mar 27, 2006

I am working on a small database for a school to track their live and silent auction. Currently I have a table set up as follows.

Silent Auction table :
Item Number
Short Item Description
Long Item Description
there are more fields that I am not interested in right now.

Mainly what I am after right now is to do some decent reporting for generating the catalog and posting the silent auction items on the web site for viewing before the night of the event.

I need to generate a report that contains the Item Number, short and long description. I need to group on Category which corresponds to the item numbers. Example:

Item Number 100 - 110 = Class Projects
110 -199 = Live Auction Items
200 - 299 = Sports and Recreation
300 - 349 = Entertainment
and so on......

I realize that had they numbered them in the 100 range I could group in my report by 100's and everything would be pretty good however, I did not have the opportuinity to be involved in the numbering scheme.

Even if the numbers had been in the order of 100s I still would need a way to say that the 100 -110 range is Class Projects.

I think I need another table and link it but I am not sure how to set up the range of numbers for the category.

Sorry to be so wordy.

Thanks in advance for the help.

Lee

Network admin trying to learn Access.

## Assigning Sequential Numbers.

Feb 12, 2007

Yo there - thought Id fire a question out regardin numbering:

I have a table players and a table team.

Players sign up to teams, and I have a report which shows each team, and the players underneath.

Team
Player 1
Player 2

Now, after all the players are assigned to a team, I want to assign each player within each team a random number, sequential.

So when I run the report, it will be like:

Team A
1. Player 1
2. Player 2

Team B
1. Player 1
2. Player 2

Any pointers!?

Cheeas -

## Queries :: Assigning A Number Based On A Range Of Dates?

Mar 19, 2013

I have a table called StockTable with the following fields Location, Status, Serial, Make, Model, LastDate, DotNumber

I also have a table called FCDateRange with three fields

DateStart DateEnd and DotNumber

For example

3/7/13 - 3/13/13 - 1
3/14/13 - 3/20/13 - 2

Im trying to figure how to write a query that if the lastdate from the StockTable falls during the DateStart and DateEnd fields it will assign it the number in the DotNumber field

## Modules & VBA :: Assigning Reference Numbers To Records Where Same Customer Appear

Nov 21, 2013

I'm trying to put together a DB for creating notifications to customers. The source file is pulled out from another system as an Excel file, which is then uploaded to Access via VBA. The user runs a query, and then sends the results (customer information) to an Excel file where we have set up a mail merge document (the notification itself).

A customer may appear in multiple records (having multiple accounts), but will only receive one notification.

What we're trying to do is give each notification an unique ID that will become its document reference number once the document is created. The number will need to be the same for all the records in which the customer appears, and they will need to be consecutive, as the documents themselves will be stored in PDF form later on in the process.

So I would like to assign an unique ID to each customer, each time it shows up when querying the uploaded source file (the query is done in a form, that has a sub-form for showing the results). Then, when exporting the query results, they will be also copied and stored to another table, along with the assigned reference number, for monitoring purposes.

The Access file itself will be split and used by multiple users (up to 9 users at a time). In theory, each user will see their own customers (each customer account is assigned to a certain user), but the number assignation will need to also depend on when the query has been run. For example, if user 1 queries the DB at 9:30AM they will get records numbered 1000 to 1050; user 2 (who queries the DB at 9:31AM) gets numbers 1051 to 2100 etc.

## Range Between 2 Numbers

Aug 14, 2006

Hello:

I have 15 text boxes on a form. After the last text box is filled in, I can get the average okay, the sum okay, but would like to caculate the difference between the highest and the lowest numbers (Range). I've tried a ton of "If" statments, but did not seem to work. Any ideas would be helpful. I searched the forum for "Range" but could not locate an answer.

thx

## Totals Over A Range Of Dates (numbers)

Oct 10, 2005

My dates are stored as numbers (long integers), because they need to be in the form YYYYMMDD, and I couldn't figure out how to enter them and keep them in that format as Date/Time.

Anyhow, I have a form with a textbox and a button. When the user enters in a date range, I need totals to show up for only dates that are in-between and/or equal to the date(s) typed in the textbox.

For example when the user types in 20050904-20051004, and pushes the button, a display of totals will be displayed for only those dates and those in-between. If the user leaves the textbox blank, and pushes the button, I want totals from all dates to be shown.

As you may know from my previous question, I am a relative newbie to Access. Suggestions about how to do this maybe even how to derive and present the totals, and especially specific code will be very helpful and VERY much appreciated.

## Qry: Summing Numbers From A Date Range

Jan 22, 2008

I need some help please. I am working on a performance management database. In this database we keep track of daily overtime and how that overtime is broken down into sub categories. For example, my staff worked 14 hours of overtime yesterday. 8 went for weather related overtime and 6 went to cover vacations. I have a report that shows all of the supervisor's overtime in a continuous list of running totals. But it's getting to big. I am trying to create a query for each supervisor that pulls the daily totals for the previous week and gives me that total sum. So far, my individual supervisor queries look like this. DATE, SUPERVISOR, DAILY TOTAL. They click on the report button and then they just count 7 days worth of numbers.

How do I create this?

TOTAL FOR SUPERVISOR X FOR THE WEEK OF 00/00/00 through 00/00/00 that pulls the daily totals out of my "overtime table"? and gives me a weekly total?

Thank you
Shannon

## Modules & VBA :: Finding All Numbers In A Range?

Jul 16, 2014

I need to create a very simple database that would just store records and produce a couple of reports. I have three tables: one with the roster , one has records of the inventory items people from the roster receive and another one contains types of inventory we have. Everything is very simple except for one part. Every time we make a record of an inventory item given to someone, it requires not only employee id and inventory type from the existing tables. It needs us to enter a serial number of an item. This serial number contains a letter and a number.

Looks something like this - M100. Many people receive a consecutive set of inventory items. For example, from M100 to M150. There is no way to have a separate table with all serial numbers because they constantly change. That is why we need to have two text boxes that would allow us to input a range of serial numbers or just one number. Then the program should separate numbers from letters, evaluate the range, create new records of numbers and then put new numbers and a letter back together into one field in the table where we have all inventory records.

I found the following code online that allows me to find numbers within a range, but it only works for numbers.

Dim varRange As Variant
Dim lngLow As Long
Dim lngHigh As Long
Dim lngCounter As Long
DoCmd.Hourglass True

[code]...

I then found a piece of code that is supposed to separate numbers from letters, but I can't find a way to make it work.

Public Function FindNum(strName As String) As String
Dim strTemp As String
Dim i As Integer
For i = 1 To Len(strName)
strTemp = Mid(strName, i, 1)
If (Asc(strTemp) < 91 And Asc(strTemp) > 64) Or (Asc(strTemp) < 128 And Asc(strTemp) > 96) Then
FindNum = Right\$(strName, Len(strName) - i)
End If
Next i
End Function

how to alter the code to make it work for my specific situation.

## Modules & VBA :: Automatically Check A Box If Certain Fields Are Within Range Of Numbers?

Nov 2, 2013

I have 3 fields on a form PU_POINTS, SU_POINTS & 2_MILE_RUN_POINTS. The user enters a number 1-100 in each of these fields. If the user enters a number in any of the 3 fields of anything less than 60 then I want it to check a fail box. If the user enters all numbers 60 or more then i want it to check a pass box.

## Category And Sub Category

Jul 6, 2006

Trying to make up a pricing database for jobs

for instance if we have

Bonds : 15mm//17mm//20mm

Downtape : copper // aluminium

I want a drop down box where you select the category Downtape or bonds, then another drop down box showing all the possible choices for bonds if you selected bonds, but wont show you any of the down tape options.

I have been trying to work out the relationships here and been getting in a muddle so any help would be much appreicated.

Juffled

## Category - Sum

Jun 18, 2007

Dear All,

I'd like to solve the following problem.

I have a table similar to this:

material1_date1_qty1
material1_date2_qty2
...
material1_date9_qty9
...
materialN_dateX_qtyW

I'd like to define categories for date (e.g. month) and summarize the quantities:

material1_period1_qty1+2 (if date1&2 are in period1)
material1_period2_qty3+4+...+9 (if date3-9 are in period2)
...

I cannot give conditions/categories in function 'sum' - how can I solve this?

BR
xxyy

## Forms View By Category

May 24, 2006

Hi, I am trying to set up a form so that i can view the employees at my workplace by the scheme they are in. I have tried a number of things with no luck. If anyone could help me i wold really appreciate it. An example would be a god send. Thanks alot.

## Category Table With Subcats

Jul 26, 2007

I have a table of categories with the columns: CatID | CatName | CatParentID

This way I'd like the ability to have unlimited subcategories.

My question is, how do I create a form that will let me enter the CatName with an autogenerated CatID, then enter a subcategory and select the first category as it's parent?

Something like this:

CatId | CatName | CatParentID
1 Tools 0
2 Hammers 1
3 saws 1

## Set Of Data Available Depends On Category Selected

Aug 4, 2005

Hi,

In a form, I am using two comboboxes per record (i.e. when you add a new record, two more comboboxes for that record appear).

My goal is to have the second combobox show entries specific to what the first combobox was, with certain caveats (see below).

My current approach is using three tables - tblTypes, tblOptions, and tblEntries. tblTypes would store the different types of entries. Each of the types in tblTypes would contain options depending on its ID.

Basically, the first combobox will list the data in tblEntries and the second combobox will list the data in tblOptions, depending on what "Entry" was selected. New "Entry's" for the first combobox would be created in tblEntries, where the ID in this table would indicate what "Options" are available for that entry. As shown in the sample table below, I want to allow more than one entry with the same "ID" (i.e. there could be more than one entry with the same options, but the entry name would be different.)

A sample of what the tables would look like is below:

tblTypes
---------
ID |Type
1 TypeA
2 TypeB
3 TypeC

tblOptions
----------
ID | Option
1 Data1
1 Data2
1 Data3
2 Data1
3 Data4
3 Data5

tblEntries
----------
ID | Entry
1 Entry1
2 Entry2
2 Entry3
3 Entry4

Is this approach sound? If so, how would I create the comboboxes to use with this set of data? I tried doing so but ran into a certain problem - that is, the first combobox doesn't allow you to "select" entries with the same ID - it lets you select the first one, but no other... (using a query that simply selects all of the entries and its associated ID - I used the ID to populate the second textbox appropriately.

If this approach sounds unreasonable, how would you approach it? And after creating the tables, how would you go about setting up the combo boxes?

Any help would be much appreciated.

## Generate A Report/form By Category

Jan 16, 2007

Hi everyone,

I am tring to generate a report or form, its generated by a combo box, which list all the categories of the products, when the category is clicked on it will generate the report or form showing all the products for that particular category. Can anyone point me in the right direction? Any help would be appreciated.

## PivotChart Category(x Axis) As Variable

Jun 24, 2006

Hi all, I want to create a pivotchart from a query, embed it in a form and use a combobox in the main form to allow the user to select the x axis value. So in a query with fields city, state, country, etc the user would be able to chose one of those fields as the category(x axis across the bottom) to display data. Is this even possible? If it is, how do I do it? As it is now I can add multiple categories to my chart- but it just makes a big mess of the chart. I can embed a chart in a form and use a combobox- but it allows me to choose from within a field- but not from among fields. Also- how do I access the code to manipulate the chart? Thanx in advance

Mar 4, 2014

I am creating a report which is about 200 pages and would like to add a table of contents for each category i have with page numbers, however i have googled and tried many different things but to no avail and what it the correct way or even showing me on a sample database.

## Queries :: Aggregate Volumes For Category

Jun 15, 2015

Basically trying to create a chart through a Query.

Table is as follow:

Applebrand Date Volume per week Category
------------ ------ -------------------- -----------

I am trying to display the aggregated volume in a chart. The volumes can change depending on the demand from the buyers and its always the latest volume for the specific applebrand that is in play.

Example

Apple A 20150101 10 Retail
Apple B 20150202 100 Restaurants
Apple B 20150303 200 Retail
Apple A 20150404 50 Retail

The chart i would like to see is a aggregated volume for category "Retail" displaying a line from 10 to 210 to 250.

Instead my chart displays 10 to 200 to 50

Is there a way a solve this or should i use a different approach?

## Queries :: Field Supplier With More Than One Category

Jan 13, 2015

I've got a table called invoice with a field supplier and another one invoice_category among others.

How can I retrieve all the suppliers that have more than two categories in the invoice table.

I tried

Code:
select count(*) from (select distinct invoice_category from invoice)

but it didn't work.

## Forms :: Calculation To A Specific Category

Mar 8, 2013

I have a form with the following:

A combo textbox with Category A and Category B.

An Amount textbox.

In the form footer, I have a textbox calculating the sum of the Amount.

This works fine but how do I create a textbox that calculates just the Category A or Category B??

## Graph That Has A Bar For Each Category Or Column That Has Dates

Mar 29, 2012

Question... i Wanted to create a Graph that has a bar for each category or column that has dates.. i want it to filter to show only the dates greater than 12-31-2011 so basically all dates in 2012.. i figured out how to do a query but i was only able to do a query for ONE column at a time so i'll have like 30 querys...is there a way to do this with one query and have each bar on the graph be for each column and have the graph showing how many people have completed these after 12-31-2011. URL...

## Modules & VBA :: Outlook Calendar Category Colour

May 1, 2014

I have been using the below code for a number of years to send reminders to Outlook and colour them red (my category colour for certain items).

I've been asked to roll out this code to all members of my dept but the category for other people assigned to the red colour is different to mine.

Is there a way to colour the item using the colour name rather than the category name?

Set OutlookApt = Outlook.CreateItem(olAppointmentItem)
With OutlookApt
.Subject = MailSubject
.start = Dte
.End = DateAdd("h", 1, Dte)

[Code] ....

## Queries :: Creating Query To Show Only One Of Each Category?

Nov 23, 2014

I am just starting out in access. I have a table that has different vehicle models. There is a "category" field that has vehicles under boats, cars, planes, heli, etc... How do I make a query that will only show the different categories that are used? So I would just want the category field showing one of each category that exists and not multiple records with the same category.

## Modules & VBA :: Send Email From Database With Category

Aug 17, 2015

Is it possible to send an email from an access 2007 database with a category?

## Queries :: Need Query For Category Keywords Using Tables

Nov 21, 2014

I have a database with hundreds of records in a main table called tblPARTS which has three fields - Part Number, Part Description and Category. I have another table, tblKEYWORDS, with two fields; a field called KEYWORDS which has words like "cable", "bracket", and "relay". The table has a second field called CATEGORY. The CATEGORY field has either the word ELECTRICAL or MECHANICAL associated with each of the KEYWORDS. So, for example, "bracket" would have "MECHANICAL" as its associated category. There are dozens of these entries in the KEYWORD table.

The problem: I need to auto fill the parts table (tblPARTS) with the correct category by looking at the DESCRIPTION field in the PARTS table which is made of several words, e.g. "STAINLESS STEEL BRACKET - 3" X 2" ". I need to find the word BRACKET and fill in the CATEGORY field in the parts table automatically via a query. There are several hundred parts in the main table.

I can do this easily using a standard update query, but it is limited to 10 IFF statements and there will be many more than this. Can I do this in VBA and if so, where do I start? I am an intermediate user of ACCESS.

## 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.