Hi, as ever, am sure this is simple...
I have a field in a table which is a code. This code is used to allocate records to certain groups. I want to run a query which then returns the records, grouped by code and displayed in ORDER of the biggest group. So if there are 20 records with Code A and 15 with Code B and 67 with Code C the Code C records should come first, then B, then A.
Can anyone help???
Thanks!
A
Data in table “tblEmployees”:
EmpName GroupNum
Jon Group1
Sam Group2
Tom Group1
Bob Group1
Hal Group2
Dan Group3
Cal Group2
With sample table above, can I write a query in Access that lists the data by GroupNum based on my criteria, say Group2 1st, then Group3, then Group1? Basically, how do I make the result show:
Sam Group2
Hal Group2
Cal Group2
Dan Group3
Jon Group1
Tom Group1
Bob Group1
Will keeping your field size shorter result in a smaller MDB file?
Or does Access only use as much space as there is real data in its fields.
Way back in the dBASE III days, dBASE would pad all your "real" information with as many spaces as necessary to fill up your field. I suspect that the MDB structure is probably smarter than that.
Another question on the same topic - I believe there is a maximum number of characters in a record (4000?). Can your field sizes add up to more than 4000, as long as the actual data, all combined, never totals 4000...?
Thanks............
..dc
i made a report based on a query. when i run the query, my results are sorted by Job Time Ascending, like i want...
when i made my report i chose job id as the main group priority in the wizard..
now in my report it displays the jobs in ID ascending, rather than Job time ascending as my query says..
i dont want to re-do the report it took ages.. how can i fix this?
Hi all,
I have a form in Access 2K that properly displays city, state and zip codes. As I type in each letter of a city's name into the combo box, I get the first city in the sorted list displayed (autofills). That's fine when there is only one zip code for a city, but when I enter "chic" for chicago I still get the first chicago zip code in my table.
It sure would be nice if I could do a "sort within a sort" so that if there were two or more records with the same city name then that whole group would drop down so I could choose one of the other zip codes. If this is doable, kindly point me to an example as I need all the help I can get.
Thanks in advance for any responses.
D
I have a query that sorts players batting averages from highest to lowest. When I run the query just by itself it sorts it right however when I open the report based on that query it does not sort in descending order like I asked. What am I missing??
View 3 Replies View RelatedI have a query that returns the following values:
Group_Name
Group_Priority
Person_Name
Amt_From
Amt_To
Branch
ID_Number
How can I select from this to get the top 10 'Amt_From' values within each Group_Name, in one query?
I can get the top 10 overall, and I can find the top 10 by specifiying a Group_Name value, but I'm hoping to find all in one query.
Any ideas gratefully received.
I think I am missing sth here, after I set my new group then I create my new user, then what! How can I specify which user to login with? Will the user be promoted for a username and a password?
View 3 Replies View RelatedFor a little light relief I thought I would delve into the joys of security groups and immersed in searches and reading material. From what I have learnt so far, yes FE / BE best approach and take time, as obviously easy to get wrong and difficult to undo.
Not looking for the answer, just a hint that I'm on the right track
The FE groups settings is where the bulk of the detailed work is required?
The BE groups settings are to ensure the correct read / write access to the tables?
I am thinking of making FE groups as BE users (smart or dumb) to simplify this end?
Any encouragement / early warnings appreciated
Hi,
I have a dataset with an age field which just stored an age. I want to run a query which will calculate the frequency based on all age groups 20-30,30-40.
Does anyone have any ideas
I have created a work order application, and the process is that it takes total number ordered of an item, divides it by a factor, and then prints x number of work orders, example qty ordered 300 = 5 work orders, 4@72 and 1@12, my question is, now I want to appended the work order number with a count number, example wo555-1, wo555-2 and so on, grouping on the work order number, so each work order start again at 1. I am stumped, can anyone help.
View 3 Replies View RelatedtblCountries -- CountryID, Country
tblAthletes -- AthletesID, FirstName, Surname, CountryID, M/F
tblSports -- SportID, Sport
tblEvents -- EventID, Event, VenueID, SportID
tblVenues -- VenueID, Venue, Location
tblMedals -- MedalID, MedalType
tblResults -- ResultID, AthleteID, MedalID, EventID
Primary Keys - Bold
Foreign Keys - Italic
This is the tables in my database, it is a database of the upcoming Beijing Olympics.
I am aiming to have a medal count for each country which is in the current database.
As it has the capabilities to expand therefore I am trying to avoid the need to create a whole new query for each country I add, also it would be preferable to be able to have all of the countries on the same report, so that I can compare the totals.
Is there some way I can count the total of each group of records (each country) for the field where 'MedalType' = "Gold", "Silver" and "Bronze". Also the calculation needs to be done before the report so that I can order by the total.
I am new to this so can answers be preferably in layman's terms.
Hello Everyone.
I have a form that gets its data from a query.
There is a field in the query called "FinalisedDate" which is a date/time field. I want a button called Current that when pressed it shows all records where the FinalisedDate field is Null which is to be the default of when the form is open.. Another button called "Aged" is pressed it only shows records that have the FinalisedDate field has a value (i.e. not null). I want another button that shows All Records (i.e Current and Aged).
I hope anyone can help. I have searched the forums but did not find a similar problem that I can copy.
Regards
This is a multi-faceted question may the answer to the first may help the second.
I have two option groups each with two options in each.
The first is called Frame 57 and has two options A ( the wizard assigned a value of 1) and B (assigned a value of two).
The second is called frame 58 and has two options C ( the wizard assigned a value of 1) and D (assigned a value of 2).
I have set enable on Frame 58 to No.
My aim:
If I choose A in Frame 57 then I want Frame 58 (with both options) to become enabled.
If I choose B in Frame 57 then I want Frame 58 to remain un-enabled.
When writing the code do I
Put it in the Click event of the FRAME?
and should i refer to the option groups by their name (ie: A or B) or by their assigned value (1 or 2).
This is what I have at the moment
Private Sub Frame57_Click()
If Frame.57 = 1 Then
Frame58.Enabled = True
Else
Frame58.Enabled = False
End If
End Sub
This seems to work though when I return close and re-open the form Frame 58 (even if it was chosen and enabled) is now not enabled even though the option is Frame 57 is correct and visible.
Hope this makes sense - any help appreciated
Hello all,
I need to randomise participants in an Access 2000 db into four groups. Let's say these are 1, 2, 3 and 4. This in itself is not the problem. The problem is that I need to keep the numbers of participants in each group approximately equal, say to within a tolerance of 4.
Can anyone point me in the right direction please?
Thanks very much.
please does anybody know how i can know inside my code what is the Group of my actual user???and what is the current user ?...
View 6 Replies View RelatedI want to import all the objects from a databse, and there are loads of object shortcuts.
Any ideas?
Hello!
I have a problem figuring out a solution for following problem.
I have a database of details and their properties.
All the details have been described in one table.
As there is a need to describe groups of details I have found myself in a hard situation that needs to be resolved.
The structure of described detail groups is as follows:
Product
Detail Group1
Detail groupx etc.
Detail4
Detail groupx etc.
Detail groupx etc.
Detail1
Detail2
Detail3
Detail Group2
Detailx
Detailx
Detail groupx etc.
Detail groupx etc.
Is there a way of decribing my products in access so that there are no limits of how many groups does a product have and how many levels of groups the product have.
Thank you
Hi all,
Simple question (I hope)...
Need to devise a way of updating a field with an ascending sequential number within a group of records of similar type.
e.g.
Say I have records which consists of numerous IDs and various job descriptions within the each ID
13000 Head of Maths 1
13000 School Secretary 1
13000 Head of Maths 2
13000 Head of Maths 3
13000 School Secretary 2
14000 Head of Maths 1
So in this case each 'Head of Maths' and 'School Secretary' sequentially increments until there is a new ID.
Any help appreciated.
thanks,
Alex
I'm trying to create a form for entering data for new items for a warehouse inventory. So far, I've figured everything out, but now I'm stuck.
I want to create an option group for two separate snippets of information: Whether a product is damaged or not, and whether a product is in numerical order or not.
I have created two bound option groups with option buttons, one for "Damaged?" (Yes or no) and one for "Sequential?" (Yes or no).
When I open the form, however, only the "Damaged?" buttons work. I can click either one, and it changes from yes to no and vice versa. However, the "Sequential?" options do not work. It doesn't matter which option group I try to click first, I cannot select "Yes" or "No" under "Sequential.
Any input would be greatly appreciated!! Thank you!
I have a database of customers for which I want to print address labels depending on what group I have entered them in. There are about 30 different groups that they could be a member of. I have entered them in to groups by using "yes/no" fields on the customer table to indicate who is a member of which set. My problem is that I know how to indentify which group is required using sql
eg select surname,address from table where GP
where GP is one of the possible groups. I cannot however see a way of selecting the group variable from a form and entering into a query, so that I can print the required label set.
Can anyone please advise me on this or point point me in the direction of an example. I tried using a combo box on form but could not get it to pass the parameter correctly. Many thanks in advance.
Regards
Peter
Been searching for an answer to this one but still cant quite get it.
I am using an option group to subtract and add percentages on an amount in a text box. This is the code i am using but there is something wrong. My syntax is out.
Me.TechsRate = ((Me.Chargeout.Text - (Me.Option17.OptionValue /100))
I am trying to calculate 5% or 10% or 15% and so on, up to 35%
Thank you in advance
I've tried looking at the Northwind DB report for an example for printing out "Page of Page" by grouping but I just can't associate there code with my DB. In my report I have different "Equip" that gets calibrated throughout the month. I print a report out at the end of the month. Most of the equip reports are 1 page but some do turn into 2 or 3 pages. Could someone look at my DB and show me what to put in the code for the report header and footer to show Page of Page for each Equip in the report.
thanks
Hello,
I'm creating a simple relational database of all the attorneys our company work with. I'd like to have three tables: "Law Firms" (firm name & other info), "Attorneys" (what firm, personal info, and what practice groups s/he is involved in), and lastly "Practice Areas" (all different practice areas with codes).
Now, my issue is that people are usually involved in multiple practice areas. Thus, I can't just put a code for one practice area in "Attorneys" table, but I can't put multiple codes in either in one field (right?) because it'd mess up the relations.
What I'd like to accomplish is for people to be able to go to our intranet and, using a form (?), select a law firm and a practice group and get a list of attorneys.
Does anyone know of a good way to solve this? It doesn't have to be pretty, since the DB isn't going to be huge (2000-2500 records).
Thank you!
HI everyone, and thankyou for reading this.
I am the secretary of an athletics club and have the task of using membership data to compile groups of athletes.:eek:
One of the fields that I have available is the date of birth of every athlete. It is entered in the form dd/mm/yyyy.
I would like to be able to query the database and display the age of each athlete.
I would also like to be able to query the database so that I can compile groups of athletes grouped by their ages as at 01/09/yyyy
These groups would be: Under 11 years,
over 10 years but under 13 years,
over 12 years but under 15 years,
over 14 years but under 17 years under 20 years.
over 19 years.
I have some understanding of using SQL view in Access but my overall knowledge must be viewed as "novice".
If you are able to help me with this problem I would be extremely grateful (as would our club), and I would also be obliged if you could keep instructions simple.
Many thanks
John:)
Not sure whether this would be easier in a query than in VBA.. but I'm new to this:9
I've got a guest list for a big party, and one of the fields "Arrival" is a DATE/TIME field where it says when the guest entered the premises.
What I want for reporting purposes is to divide guests into nine "arrivalgroups", a number field I've added to the table in casu.
The groups look like:
117-18(or <16:00 AND >=18:00)
218-19
319-20
etc. all the way up to 9, last group being 01:00-02:00
That is, if the guest arrived between 20 and 21, the table field "arrivalgroup" would be set to 4.
Any easy way to do this?