Quick Query Question
Nov 9, 2005
I have a table with 3 fields... ID, Name, Hours
ID is NOT unique in the table
IDs Can have different names
Hours is what I will use to find the total hours
I want my query to group by ID only, and show the SumofHours
BUT... I want to show the name as well(without Grouping by it), I want my query to Show me the name that is MOST Recurring.
So lets say that ID '1' occurs four times, with the Names...
'Mark' 'John' 'David' 'John'...
Running the Query I would want to see 'John' under the name field Right Next to the now Unique ID '1'
At first I thought I could just use the Max function from the drop down menu, but that only uses the one that is alphabetically a maximum... I need it to be a maximum by count..
Any help would appreciated,
Ayyad
View Replies
ADVERTISEMENT
Jan 13, 2006
Just a quick question.
is better to use a query for a form or just link straight to table.
e.g you have tblColour, this table has ColourID & Colour.should you build a query including all fields then build a form from that query?
Thanks
View 2 Replies
View Related
Jul 29, 2005
I have a query statement:
Final Tax Free Amount: IIf(Left([Final Tax Code],1)="k",Right([Final Tax Code],Len([Final Tax Code])-1)*-10-9,Val([Final Tax Code])*10+9)
If I wanted to include: If final tax code = BR or NT then = 0. How can I add this to this statement?
thanks
View 1 Replies
View Related
Jan 15, 2006
hi there
could somebody help in the query design in order to query table 1 (image attached), by showing the maximum weight for each component_id, but also include other details such as the date when the maximum occurred and the event_id, the result as query 2 (image attached)
if i just select component_id and weight, i can do this by selecting max weight, but when i add the date and / or the event_id columns then i get duplicates in the component_id column
any help would be greatly appreciated
View 1 Replies
View Related
Aug 23, 2006
Thanks for helping me out with this question. Im sure this is pretty easy to do. I have a query with a few paramaters set up... one of them runs a custom function all i want to do is this
query one
Average water flow:Avg([WaterFlow])
when run this just shows me the average water flow for the database... now if i have another criteria i want to base off the average how do i get the query to realize that. for example it would look something like this
[Average water flow]/[currentFlow]
how do i reference another criteria? hopefully this isnt to hard to understand.
View 1 Replies
View Related
Nov 4, 2006
hey guys quick one
when creating a table in SQL how do you set a variable to yes/no i tried the BIT command but didnt like that so is there any other way?
cheers guys
View 2 Replies
View Related
Oct 16, 2005
Hey guys, I'm just doing this database and have suddenly realised I dont know as much about MS Access as I thought!
Q.1 - I need to run a query that displays the top 5 records in a table, how would I go about doing this?
Q.2 - In my tables, I have of numerical data like the following...
http://img276.imageshack.us/img276/2695/11ch1.png
is it possible to make a Totals column which automatcily adds up the data in the Fraud, Competition, Loss of data, etc rows? (similar to MS Excel if possible)
View 6 Replies
View Related
Jun 20, 2006
HI
I am creating this bloody db and I have created a form and when I open it, it shows, record no one, what I want it to do is open blank ready for a new bit of info to be put in. I know this is an easy one but im sick of the sight of access this week
With thanks
View 5 Replies
View Related
Jan 17, 2007
Hello
Great website. Does anyone have any information on the leafs (http://a-cholesterol-diet.com/index.htm). I have been looking all over the place but no one seems to have any information or listings of any websites, any help would be greatly appreciated.
Thx
View 1 Replies
View Related
May 30, 2007
I have done a little database for someone so they can keep track of available dates for their apartments in Turkey. They want me to add it to their website so people can look for themselves. I know I should join a beginners web design forum, but can anyone tell me how i actually log into a website? I have the wesite address and the password. Thank you in advance Ben
View 5 Replies
View Related
Jun 5, 2007
Hi all,
I am just wondering why Modules have the name of bas at the start of the name. frmForm and rptReport makes sense but why bas for Modules ?
Thanks in advance,
Mitch.....:confused:
View 3 Replies
View Related
Oct 29, 2007
I just joined and wanted to say hi to everyone. Also, I noticed that the forum runs off my screen.
Anyone else notice that?
melanie
View 2 Replies
View Related
Jan 9, 2006
Hello,
one of tables requires me to store sickness details, the sickness codes are all unique, they do however start with at least one 0 for example code - 000003 Description Stress. When you enter a code access automatically trims of the 0's to leave a 3.
Is there a way to override this im unaware of?
View 14 Replies
View Related
Apr 2, 2007
i am using the following query to make a table. i was wondering if anyone knew a way to have the table be named from data entered into a text box in a different form. so when different people type in different names a new different table will be created. thanks
CREATE TABLE tbalename (First_Name TEXT(20), Last_Name TEXT(25), dob DATETIME, Constraint Employees_PK Primary Key (First_Name, Last_Name, dob));
View 2 Replies
View Related
May 4, 2006
Bit of a newbie so help appreciated.
The system I have developed lists jobs (as in jobs that staff members need to go and do) in different towns and cities. These jobs are set as active or inactive depending if completed.
I need to run a report that allows the user to find active jobs in a particular town. I have made a query and by setting the status field to a criteria of "active" this ensures that all results in the query are only active ones.
I cannot find a way to allow the person running the search to enter the name of the town that they want. This would then allow the query and then report to list all "active" jobs in a particular town.
I have a table containing all the towns so hope I can offer the user running the search a means to either type the name of the town into a box or even better (to reduce typo's) be able to choose the town from a combo box.
Once the town has been selected, the report will list all active jobs in that particular town.
Thanks
Can anyone
View 1 Replies
View Related
Jun 21, 2007
I'm writing a field where it shows a combination of many fields.
For example, when a user types in "abc" in field 1, "def" in field 2, "ghi" in field 3, I will have to combine the three fields into "abc/def/ghi" with slashes in between them. However I don't want to have any slashes at the front or at the back the field.
There are unlimited fields of "abc" "def" "ghi" "jkl" "mno" etc. Any idea how I can write the code? I'm thinking using if statements. Is there other ways I can do this?
Thanks!!
View 9 Replies
View Related
Feb 12, 2008
I know I should know the answer to this but if you are creating a query that includes individual fields as well as calculations based on some of those fields, do you select the source field or the query "field" when using the expression builder?
View 5 Replies
View Related
Apr 22, 2008
Hi,
I have a database showing car details, and need to show all records whose MOT is due within the next 30 days/one month (from today)
It might be similar to my first question.
Thanks in advance
Dan
View 4 Replies
View Related
Nov 24, 2004
My Mgrs. have asked me to set up a spreadsheet to keep up with inventory for a certain section of the business. And I figured that in the long run it would be easier to set up a database instead of a spreadsheet. We have specific customers with specific products to keep together; and I have figured out how to fullfill this .... my problem lies with the actual inventory.
The questions:
1) Can I keep an Available tally for all products?
2) To ship out and receive in cargo do I need to make 2 tables or just 1?
I have to keep up with the location of the goods in the warehouse and I'm not sure if I should include that with the product table or a seperate inventory table.
I am not sure if I will be using the database to create 'orders' or what we call bills of lading to ship out the cargo so I'm a little lost on how to keep up with the quantity of each product in the warehouse. There is no 'reorder quantity' or anything like that. I just have to keep up with how much of each product I have in the warehouse. Then I also have to be able to keep track of what is received into the warehouse and what is shipped out of the warehouse.
Does any of this make sense? I'm so confused myself that I'm not sure that I am able to explain what I need or what my questions are without confusing any one I'm speaking with.
View 4 Replies
View Related
Jun 15, 2005
Hello everyone, I am new to the forums. I need to learn Microsoft Access very quickly, but I do not understand a thing! I have a list of features of Access that I need to learn: what they are and how to execute them. Could anybody help me please?
The features include:
Field-type
Field-insert/delete
Query – create using criteria
Record – insert/delete
Sort-simple
Structure – file/table
Objects types
Form – columnar
Form – tabular
Form – header/title
Report – create
Report – query report
Filter
Thanks
View 3 Replies
View Related
Jun 12, 2006
Hey guys,
I'm having two small issues with my database. The first is, I can't get my drop-down to display content on the Form "frmSearch" under "Cast".
And the second, not really a problem, more of an inquiry... on the same form, is there a way to set a default value in the "released before..." and "released after..." of "(enter year - xxxx)" and have that value not interfere with the search feature if it is left as default?
Any help would be greatly appreciated. I'm very new to database creation and an eager to learn about these things. Thank you!
View 3 Replies
View Related
Apr 1, 2007
I'm importing a rather large CSV file that has many employees, repeated on several lines (one line per day) that contains performance information.
If I want to create a separate table that will contain basic information about each employee (for inclusion on reports), would that be accomplished with a relationship?
I can setup a query to pull up the information by employee name, but I of course receive more than one row since on the import an employee can be listed several times (multiple rows).
The two tables would not share any data, I just want a setup so when a report is generated and a specific employee appears, this information is generated with it. Thanks :)
View 3 Replies
View Related
Nov 29, 2004
But not easy for me, as its been years since ive done this kinda stuff. I got a field called 'CreditCard#' in a table called Customer. The data type of this field, i've set to 'Number' but how do i format it so it only allows the user to enter in a 16 digit number?
View 2 Replies
View Related
Jan 22, 2005
Hey all. I've got a field that I'd like to put a paragraph or so of text into and I'd like to just click on that field and have an arrow open up a box with all the text for easy viewing... (i hope this makes sense). Anyone know how to do this? (I'm sure it's easy if it is doable) thanks!
View 4 Replies
View Related
Feb 21, 2005
just a quick question guys,
If i had two tables;
tblCar
CarID
CarName
tblDriver
DriverID
DriverName
how would i put these into a tblCarDriver, would i be right in creating
tblCarDriver
CarDriverID
CarID
DriverID
then how would i set up the fields for this table?
e.g when i use the lookup wizard for the CarID & DriverID fields in tblCarDriver would i select all fields from the Car table and all fields from the driver table or just the CarID from the Car table and DriverID from the Driver table.
How would i show this in a form!!!! maybe by query and a dropdown box?
(e.g Ford, Dean Welch)
Thanks in advance....
View 2 Replies
View Related
Mar 2, 2006
Just cant seem to find anything on this although i'm sure there is?
I've got a query and with one table in it or the other the records show up fine, but as soon as i include the second table the results come up blank.
What am i doing wrong?
Just cant seem to think?
Thank you in advance
View 3 Replies
View Related
