Hi All,
I am trying to perform a query on two tables, with the result being a Dept Name & Dept Number. One table consists of an Index Number & Dept Name, the other table has Dept Number. The index Numbers & Names 14 thru 37 in first table correspond to Dept Numbers 1 thru 24 in second table.
Sorry if this is an easy one..
Thanks in advance :)
Kappa
Well, here's my database :
Name : String
Address : String
I want to add an automatically generated column, "no.", to show which number it's in. Like this :
No. Name Address
1. A Unknown
2. B Unknown2
The problem is, I can do that with Auto-Number, but if the user delete row 1, the number in row 2 is still 2, not 1.
Is it possible to generate query that have "no." column in it ?
creating sequence number in a query using a starting number. For example i want to use the starting sequence number as 5
---SEQNUM---------COLOR-----------
-----5------------------blue--------------
-----6------------------red---------------
-----7------------------orange-----------
-----8------------------black-------------
I have a field that is giving me the number of business days between a period of time and then I want to subtract that number - the person's PTO time to see the actual days they were available...when I simply type the number in (see below) it works great but I want to set up a prompt that will ask me how many PTO Days to calculate as it will be different for each person I am quering...is this possible?
View 9 Replies View RelatedHi.
I have a form (frmdetails) to store job details and another form (frmMaterials) to add materials to each job. They are both linked via a field Project_No
I have a query (QryTotal)that when run will list the materials used for each project, that is all OK.
What I am tryng to do, is add a text box to frmdetails, that will just tell me how many materials have been booked against each job. Just a running total that will tell me that for project number 100000, 4 items have been booked.
I have tried using subforms (using =count(*) ) but this doesn't seem to be working. I also added =Count(QryTotal!Material) to a text box but this doesn't work.
Can someone help?
Thanks.
Frank.
I would like to create a seperate field for each record in my query which would number the results of the query in ascending order, say from 1 to 100.
Is it possible to do this in a query?
I imported an large Excel file with a date column consisting of 3 different date formats and need help on a query to extract the number of days. Examples of the date on that column:
7/6/2006
7/1/06-7/31/06
7/1-7/5
Appreciate any advise.
Can anyone help me which I'm trying to query a field called address number that contain only number for an address. I want to query out all the even and odd number from this field. The result should be two separate report of all the even number with street address and odd numbe with street address.
Thanks.
I have a question regarding the number of fields in a query. I have a query that is looking at production scrap by categories. There are fifteen categories of type of scrap and in addition to these I am using the query to work out the total % of total scrap for each category. My problem is there are not enough fields to do all the calculations. Is there a way of adding more or is there a better way of doing what I am trying to achieve?
View 10 Replies View RelatedHey all, i just would like to know if there is a way to have a fixed number of records in a query.
Let's say, example (a), a query returns 2 records (qry1); i would like to have my query (qry2) to have 5 records, so the first two records will appear and then 3 blank records. (b) A query (qry1) returns 4 records, then this query(qry2) should return the 4 records plus a blank record
.........qry1.......qry2
a)|>record1....|>record1
...|>record2....|>record2
...|*______....|>______
....................|>______
....................|>______
....................|*______
.........qry1.......qry2
b)|>record1....|>record1
...|>record2....|>record2
...|>record3....|>record3
...|>record4....|>record4
...|*______....|>______
....................|*______
Why do i want to do this?
Because i want my report to show 5 records so when i print it i have those blank spaces to fill if necessary.
I want to have this:
______
lrecord1l
lrecord2l
l______l
l______l
l______l
Instead of:
______
lrecord1l
lrecord2l
What im doing now is creating a temporary table and add the records to it plus blank records.
What i used to do was add blank records in the same table meting the criteria of the report, so they will appear. After close the report, those records would be deleted.
I would like to know if theres another way, like a command in sql, or maybe some property on the report. What ever is most efficient and a good database practice. Thanks in advance
I have a table that keeps track of how many times an account is listed. I want to write a query that will only return the greatest number of occurences for that account. For example if acct 12345 has 3 occurences in the database I only want to return the information that is associated with the #3 occurence. As stated there is a field that calculates the number of occurences for each account so in this case there would be a record for 1, 2, and 3 occurence so I only want to see #3. Hope that makes sense. Thanks!
View 3 Replies View RelatedIs it possible to query using the week number in the year as criteria. For example instead of putting 13/05/05 to 20/05/05 can we use week 12 - the idea is to save on keying info in each time.
View 5 Replies View Relatedi have a query which looks at 2 fields: "employee number" & "agency number", i would like the query to list the employee numbers in order first (1,2,3...) then the agency numbers in order (100, 101, 102...). ive changed the ascending/ descending order for them both but doesnt help. Its mainly the form that looks at this query i would like to fix so that is shows all employee records in order of employee number first and then underneath all agency numbers in order of their number (some employee numbers & agency numbers are null) hope this makes sense , any help would be great
View 7 Replies View RelatedHi all,
I want to retrieve a set number of records from a table, using a query. I'm not sure of how to do this.
I can't use the ID, because the table gets its data replaced contantly by imports (so the ID doesn;t start at 1...)
I want to be able to retrieve, say, the first 25 records on that table.
Any help is appreciated
Hi,
Ive done a search for this and couldnt find any suitable answers. Is it possible to add a function to a query that auto numbers each row returned (so if your query has 10 results, it numbers the lrows 1 to 10)??
Thanks
Greg.
Greetings,
I am trying to write a parameter query ... I have multiple fields the users *could* search, but they may want to search only one, and I am having a struggle figuring this out.
Ex. fields to search are Gender, Age, Date, etc., and I have created a parameter query that includes parameters for each of these fields. However, if I want to see all the females, thus leaving the other two parameters blank, I have no records in my result set. Clearly, I am doing something wrong -- I have searched the forum extensively before posting.
Thanks in advance, I am sure I am being simple and missing somethign....
Here is my relationships and a copy of my SQL for this query. Why is it displaying the PK number (RaceClass) instead of the word.
My table for RaceClass has the number, the query has the number but my form has the word.
Many thanks for any help.
Tee.
I have a query that captures date values from a table based on a between function that works fine. What I would like to do is associate a sequence number fro each date value returned and either perform a make table or leave it as a select query. If I create a table I know that I aam able to add an autonumber field but the query is executed frequently and performing the addition of the autonumber field is not feesible. Any thoughts how I can generate the sequence number each time the query is executed?
Thanks
Hello,
I have a text file I'm querying that stores a field as a text.
ComponentID:0000000242
I'm inserting this data into a table that stores this value as a number. Thus truncating the leading zeros.
ComponentID:242
I would like to create a query on the table using an InnerJoin on the text file column:ComponentID:0000000242 and the table column:ComponentID:242 as the unique identifier. Doing this join gives a case error because of the different data types.
Can format ComponentID using a query so it reads from my table as 0000000242 and do an InnerJoin to the text file. In vb
String= Format(242, "0000000000")
But I'm finding it difficult to get the query to format this correctly.
Unfortunately, i'm unable to change the column data type in the table structure and am looking for a work around. Is this even possible? Any ideas?
Thanks.
I code a field that shows different codes. Most of the codes are 5 digits but there are some in there that are 4 digits. What would I type in my query to just show the fields that have 5 digits.
View 3 Replies View Relatedhi there
how many columns is the maximum in a query?, because i have one that has 26 columns , but i need some more... what can i do?? is there a way to add some empty columns to a query??
thanks
I need to query a field for any records where this field has less that 10 characters. Any suggestions? The reason is depending on the type of customer the field can be 10 or 11 digits. IF there are less than 10 but not blank they need to be pulled for correction.
View 1 Replies View RelatedI want to have a question in my query that does not get answered by date but by a concatenated field named "MonthYearPeriod".
We always forget how many days in a month. But we remember the month and year. So for say a Sales Range of Nov 2006 to Oct 2007, we prefer to respond to the criteria question as: 11-2006 and then 10-2007.
FiscalMonth and FiscalYear are both Long Integer number fields in the table.
Query field concatenated:
MonthYearPeriod: [FiscalMonth] & "-" & [FiscalYear]
criteria line:
Between [Month-Year START DATE] And [Month-Year END DATE]
However, i get other crazy time periods. So this is not working. Maybe on the Query field box, I need extra coding to convert the number to text?
I assume that a Dash sign between the month and year are fine.
Thanks
Hello all,
I have 2 tables:
- Employee
- PackageTracker (Contains PackageNumber, date received, date sent, status)
I would like to query:
- which employee has the package
- How long have we had the package (calculate only business days)
- What is the status
I would like the things mentioned above to be ran in one query.
I am stuck with the calculations. I found a macro code to calculate the business days but I am stuck.
Please advise.
Thank you,
John
hi All
I need to have an auto number in an access query, this is not the auto number already existing in the table. I need a separate auto number field in the query which begins with 1 and is in increasing order.
Any help will be much appreciated
Hope someone can help
I have a small test database which I'm doing for our dispatch dept.
It contains: Order No. / Customer / Ship_Date / Serial numbers (multiple)
I'm looking for a way to create a search query that will search though all the records to find a particular serial number.
There fields in the table are:
Shipping_Date
Customer
Order_No
Pallet_ID
Serial_1
Serial_2
Serial_3
Serial_4
etc
I need it to come back with the order number and pallet that it was shipped on.
Any ideas?
Thanks,
Paul