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.
I have a field called "Project #" that needs to generate a unique number each time a command button is clicked. For example, when the button is clicked the blank "Project #" field is filled in with "653001". After the "Project #" field is filled in the button also runs a code to create a report exported into Excel. This report will only have the records with the Project Number of "653001". Then, when more records are added and it's the end of the next week, the button is clicked again and the "new" blank "Project #" fields will be filled with the next Project number which would be "643002". A report will again be created but now with only the records with the Project number of "643002". The first three characters will always be the same for the Project Number "653" but the last three will increase whenever the button is clicked to update any new records that have a blank "Project #". So, it should look like: 653001, 653002, 653003, 653004, etc.
I know it sounds confusing but I hope someone can help.
Need a sequential number on report. Have an unbound text box and set it to list a running sum over group. The twist is that I'd like to have it start with an assigned starting number [Starting Number] and then add 1 to each subsequent record. It works for the first record but then the next record is last value + [Starting Number]. Basically it is a check run where I would list the starting number and the report would do the rest.
I have a list of what should be sequentaia numbers, but I am finding instances were a number seems to be missing..Is there a way to query the whole dataset and find all missing instances without having to trawl he list?
Ineed to track Trailer security seals that are sequentially numbered with 8 digit numbers...They are physical seals and no efile with the numbers to upload into Access...is there a way I can put in a range and Access will populate the rest in a table?
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.
I am trying to create a database that will have records that include dates and a number assigned to that date. ex., the 3rd day of the quarter will be numbered 3 and the 43rd day numbered 43. I will do this via table.
After running a query that will identify a subset of records, I only want to see those records that are part of a 10-day or greater span. There could be multiple 10-day+ spans over a given quarter.
I need to be able to enter a number of orders that would all be the same, but they will have different order numbers, in sequence.
I'd like to enter all the information in a form, then have "First Order Number" and "How Many?" fields, and have the system grab the detail, apply it to the order number entered, create a record in the table, then reapply the detail to the next sequential number, repeating the process until it has created the number of orders shown in the "How Many?" field.
I am at a total loss here, and any help would be GREATLY APPRECIATED!!
I have a database for quotations. The database automatically generates a new quotation number every time a new quote is started. This works great, and I am very happy. My next task is to allow the employee to pull up a quote that has already been generated and edit it. I would like for the new quotation process to be followed step by step, but with all of the information already filled in.
This will allow for any edits that need to be made, and keep from having to re-enter a lot of data. I want one thing to change, which is the QuotationNumber. It is currently formatted by "yyyymmdd-01" for the first quote generated on that day. I want the edited quote to have a QuotationNumber formatted by "yyyymmdd-01a". For every edit that letter change going through the alphabet in order. How would this new QuotationNumber code differ from that of the Other?
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.
I am trying to create a query to find missing sequential numbers in a text field. I am using this specific field as a case number which is designated as two letters, the # sign, two digits indicating the year, a dash, and then a four digit number; For Example: AB#13-1234.
The reason for this query is to tell the user of this database that a specific case number has yet to be entered and needs to be. The case numbers are unique and will never be referenced more than once.
My table name is "MainDataTbl" and the field i'm trying to find the missing case numbers is titled "CaseNumber".
Basically what I have is a database for tracking/logging parcels that arrive to the office. I want to be able to generate a reference number based on the date of arrival: i.e. the reference number should be ddmmyy### where ### is a sequential number. I know that I could just use the primary key's autonumber, for the sequential number but if I do this then the sequence will not restart at 1 on each date and because we receive a lot of parcels the reference number will grow to be too big to print out on the collection slips in just a few months.
two tables (one with the date and staff on duty that day and the other with the parcel's info') with a one to many relationship
I also have a query (Named: FullLog) that picks up the following data from the tables:
The field named Count is a DCount function that I used to find out the number of times each date is repeated. This is the Expression that I used:
Count: DCount("*","FullLog","DateReceived = " & [DateReceived]) [Note that DateReceived is first converted into a string using CStr()]
This is as far as I have been able to get, I have been looking for weeks for a solution to this problem but I have yet to find one. I don't even know if the DCount function is the correct way of doing it, I did read somewhere that this produces a very slow query.
Effectively what I want to be able to get is something of that resembles the following
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.
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?
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.
Hi, Complete newbie question, I have various yes/no boxes that i want to give a numeric value. The i want to add up these numbers and report it back to a text box.
Any help you can offer would be greatly appreciated.
I think this should be a simple question, but I just cannot figure it out. I have a form which contains a check box. I want to set the value of a field in a table to 0 if the box is unchecked and 1 if the box is checked. How can I do this
In my very first thread i bring a doubt that persutes me since laste Friday. I have search in the web an in this forum but, unfurtunantly, I didnīt find a answer for my problem. I hope some of you can help me :)
I am currently trying to construct a Data Base which will keep between 1.000.000 and 5.000.000 registries, and will have about 80 to 90 Fields. As you can see, its very much information to be hold and i need to ensure that the queries, that will be made to this DB, will be the simpliest and fastest as it can be possible.
Anyway, my problem is the folowing one:
I have a table, named Person, and in that tabe I got a field named Age and other on named Desig_AgeGroup. I want to assign the Age to the correnpondent AgeGroup automaticly (e.g.: if there is a registry in the field Age of the table Person thas is equal to 12, I would like that in the Field AgeGroup, in the table Person, appear " Between 1 and 14 years old" after that i typed 12 in the Age field, or after I imported Data to the field Age). For this proupose I had created another table, named AgeGroup, whit the following fields: Cod_AgeGroup, Desig_AgeGroup, Age_min, Age_max. At this point, my idea is to compare the Age, from the table Person, whith Age_min and Age_max, from the table AgeGroup. However, the only thing i have acomplished whit this method was a Text box that apear in the field AgeGroup whith all the Age groups that I had specified in the field Desig_AgeGroup of the table AgeGroup.:( Can someone tell me How can the Age groups apear automaticly in that field?
I hope I have been explicit...and sorry my poor, poor english I aprreciate, since now, any help i can get from you.
I notice that this topic has been done to death.....I have a database that is embedding the pictures and the predictable problems has manifested...the database is swelling up....
I'm learning to adapt the database to include the file location in the form and have the picture box refer to the file location, rather then embedding the picture into it....the problem that I have is that although I know how to do it because I get it.....this database will be run by a number of people, many of which don't have much experience and it must be simple to input the file location. Is there a way to simplify putting the file location in the text box aside from typing it in manually (a search window; like the one that appears when trying to open a file or insert an object, perhaps)....
I have a form that has a button. This button is used to open a report. The command used to open the report refers to a query and sets the criteria so the report generated only returns values the button asks for.
I know how to set the criteria of a query using VBA, but is there a way to set a field that has no data to now have data?
I have a field in a query called Type and it is set to Null (SQL: Null As Type) I would like to do this in the VBA, but subsitute the Null for a value that would be specific to the button.
Hi, I've lost touch of Access few years back & now I'm back again using Access... kinda rusty & yet rushing for a proj... so I'll very much appreciate if someone can help me on this..... :)
I've a database storing inventory details & after adding these details, i'm supposed to have another form where i'll start assigning these assets to different employees..& of cos, i'll need to edit this form over time if the employee left the company or asset is being transferred to another person. However, this asset list is quite comprehensive with thousands of records, so it isn't feasible for me to list all assets at one go & start assigning different asset to different employee... any idea how I shld go abt assigning? shld i minimise the search by invoice key & track no? (each invoice contains a few grp of items & each grp will contain several items, therefore, i've assigned an invoice key for each invoice & track no for each item.)
Hey to all. I have this form in which I need according to a user selection to assign a picture and display it on the same form. Is this possible? How do I do this? Or can I display the picture to another form or a report based on the first form?
I have a database that will be ran over a network. I want the freedom to add fields to the table if needed later, but I don't want anyone else to have the same permissions as me. I made an attempt at using the wizard to assign permissions, but it didn't turn out the way I needed it to. Can someone help me with this please?
Hi I am assigning a value from a table to text box on the form through VBA. It gives the following error :
Run time error 2115
The macro or function set to the BeforeUpdate or ValidationRule property for this field is preventing MS Access from saving the data in the field. I have no BeforeUpdate or Validation event for the text box. What else could be the cause ?
The text box is unbound and I use ADODB connection. Please assist.
I've built a query that selects certain customers based on a purchase date. I now need to assign these customers to various sales reps and display the customer information to them in another form that they can alter information in.
I was going to put my sales reps id into an array and loop it through the query updating it. Let's say i have 7 sales reps working that day, I would then enter their id's into fields and have the array assign these numbers into the query Sales Rep Id field for each row.