We are trying to use Access to generate, job, task and craft numbers in Access. Job and task will be unique. Craft numbers can use the auto number function. We need the numbers to be 4 digits for Job and Task.
View RepliesBasically 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:
Name - Description - Size - TrackingInfo' - Staff - DateReceived - Count
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
DateReceived - ReferenceNo
051013 051013001
051013 051013002
051013 051013003
061013 061013001
061013 061013002
071013 071013001
071013 071013002
071013 071013003
071013 071013004
071013 071013005
081013 081013001
081013 081013002
091013 091013001
101013 101013001
I am in the process of creating a DB for customer names, job numbers and document numbers, but have run into a problem right from the start.We are using a naming convention of the first 3 letters of the customer name, and 3 numbers after that. Eg. Customer names Allstar, Allpro would be All001 and All002 respectively.
The job numbers would then be sequential numbers, for each customer. So the first job for Allstar would be All001-001 and so on.How do I get Access to check/create the customer ID's? And then how do I get Access to check for the next job number for each customer?I was able to do this in Excel with the following;
=LEFT(B12,3)&COUNTIF($B$3:B12,LEFT(B12,3)&"*") in one cell
=IF(B12="","",LEFT(D12,3)&TEXT(RIGHT(D12,1),"00")) in another to add the preceding zero's.
Obviosuly , Excel is not the best way of tracking customers, jobs, documents etc.
I am using a API to generate a random / unique directory. This an API form a 32-bits environment.
I know that you can still use it when declaring it with PtrSafe
This is the code I am using:
Option Compare Database
Private Declare Function GetTempPath Lib "kernel32" Alias "GetTempPathA" (ByVal nBufferLength As Long, ByVal lpBuffer As String) As Long
Private Declare Function GetTempFileName Lib "kernel32" Alias "GetTempFileNameA" (ByVal lpszPath As String, ByVal lpPrefixString As String, ByVal wUnique As Long, ByVal lpTempFileName As String) As Long
[Code] .....
I have a table with data like this
Field_1,Field_2,Field_3
A,B,5
A,B,3
A,C,7
A,C,6
X,Y,4
X,Y,3
I need a report where I can only see
A,B,5
A,C,7
X,Y,4
That is Group by Field_1 and only show the records which has Field_2 with the max. value of Field_3
I am pritty new to access and have been tasked with creating a database !
I have two tables one called main which has 26,000 claims (listed under there part number).
The other table called part numbers is a list of all the part numbers in the main table but with no duplicates (126 part numbers)
I need to somehow count how many times a part number apears in the main table. Maybe display a count in the part number table as a extra field ? then i could put this field into the form i created to browse the parts numbers (to show how many times the part number appears in the main table)
Need to generate a unique reference number each record wise while data entering by a form. How to do this by using a command button ? After entering all fields related to the record, when click on the command button, unique reference number for that record need to be generated and save with all other data of the records. in a later event, need to recall the record by using this reference number and also need to make relation ship with some other table by using the reference number.
View 6 Replies View RelatedI didn't know which forum to post this in.
I'm stumped.
The inventory table has both cabinet assemblies and their components (tblInventoryMaster) in one table and a table that has what components go with each assembly (tblInventoryAssembly)
When a cabinet is sold, I need the ordering process to commit the components to ship (but still show the cabinet only on the order)
I've attached part of the database in question (Inventory tables, Order Form & relationships)
I have a report that prints blank serialized forms and I basically need a query to generate a field where if I request 4 sheets to be printed the list generated would look like this:
ClaimNo
1
2
3
4
It seems like this would be simple, but I'm a noob.
-aldo
Hi All,
I'm designing this database, in which, I have to give each record a unique random (or sequance) number of 5 digits, giving that the first digit must be retrived from another field.
Please Help, I need this ASAP!
Any help will be very much appreciated!
Thanks,
CS.
I have a range say 12.03 to 13.11.
I need to generate all numbers falling within this range with 0.01 increment.
(This increment is based on the numbers; if the numbers have 2 decimals, it should be 0.01. If the numbers have 3 decimals, the increment should be 0.001. Both the numbers will have the same number of decimals)
The answer is: 12.03, 12.04, 12.05, etc. etc. till 13.10, 13.11.
Is there a way we can do it in MS Access ?
I have a table of order lines. Each line of data the order number is repeated for every line. I need to just pull out one line for each order number. I assume a query would do this but I don't know how to create it. The order number is numeric. Can some one show me the way?
View 2 Replies View RelatedHey guys,
Hope you can help with this one. I have a form called "frmPatientID", based on a query "qryPatientID" and table "tblPatientID".
I have a field called HospitalNo that is set to "text" and contains the patient's unique hosptal number of 123456, 008777, etc.
However, although the above numbers have to be unique, if the patient is out of the area they get a number of 000000 and this is not unique (i.e. more than one patient can be out of the hospitals area).
So how do I get the database to control this via a rule? (As in everything NOT 000000 needs to be unique). Is it on the form on an AfterUpddate or can I do it in the table?
Many thanks,
Rusty
:D
Hello,
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.
Thank!
Elizabeth
I need to automatically generate a 5-character value for my Business Key. Without any user interaction.
2 character -> from 01,02,03,04 to 05
+
3 character -> Sequential Number (001,002,003).
The Sequential Number must reset on each new day.
I need to get an average repair price of multiple part numbers with the same root number i.e.
8 each 8TJ124GGN1
4 each 8TJ124GGM1
7 each 8TJ124GGP1
First I used a query to average the repair price of each subset of numbers, and then queried the query to average all the subset prices together.
If I simply use the AVG function in the first query, I can use it again in the second query to get the average price of all the subset price averages. HOWEVER, if I use the expression:
AdjustedAvgLabor: Int((Sum([LaborPrice])-Max([LaborPrice])- Min([LaborPrice]))/(Count([LaborPrice])-2))
…to get a more accurate average by throwing out the high and low values, I get a:
“…expression too complicated to be evaluated”
when I try to average the averages in the second query using the AVG function.
I don’t know why the expression is “too complicated” since the first query has already completed its computations before the second query begins its AVG function.
I have been working on a simple data base for some time now (beginner level) and am still trying to improve it. I would like to do something but before that I would like to have your opinion to know if it is even possible?I have a query QryMainReport:
Start Date/Time
End Date/Time
Employee
At the moment this is what the format of my report looks like (I removed other unnecessary fields):
StartTime----------EndTime---------------Employee
12/06/2014 01:00--12/06/2014 03:00------John Smith
12/06/2014 04:00--12/06/2014 06:00------Jane Doe
13/06/2014 02:00--13/06/2014 05:00------John Smith
13/06/2014 08:00--13/06/2014 08:00------Jane Doe
I would like to do as a report. (Dates would always be from Sunday to Saturday). I am not sure it is possible to do that. I suppose first it would mean:I would have to do a query to separate the times from the dates?I would have to find a way for Access to find the unique dates and unique names?Does it mean I have to use cross tab queries?
I have what I think is a difficult problem to overcome...
I am designing a form to create an invoice. The user will select a workstream and a date range in form frmInvByHrs. Within this I want two sub-forms, one is frmInvByHrsTsht and the other is frmInvByHrsBill. I want the first one to display all the staff and their hours done, and the second one to be in data entry mode where you can enter the hours you want to bill. Each sub-form is based on a separate query.
Is it possible to do this? ie. to have one sub-form in data entry mode, and the other not? It seems to me that the data entry mode is controlled by the MAIN form regardless of the sub-form settings!
If this is not possible, do you know how I can acheive this?
Thanks
S
Hi everyone,
I have refined my query from previous threads to involved a module function. This calculates more acurately no of working days between dates and takes into account a holidays table. (All credit to Arvin Meyer on the module:) )
However because the Leave Year starts at the 1 July and finishes 30 Jun I need to compose the date for any current year Year(Now())
Enclosed scrdmp shows my query design. I can easily get it to work as you see it, but obviously as each year rolls over, the year needs to change.
Have looked at many posts but can't find what I'm looking for. This one will get me over the hurdle.
Many thanks,
Excel in Access (Part 1) (http://www.access-programmers.co.uk/forums/showthread.php?p=671226)
Excel in Access (Part 3) (http://www.access-programmers.co.uk/forums/showthread.php?t=143970)
Video Version HERE: (http://www.access-programmers.co.uk/forums/showthread.php?t=144045)
How to use the Normalization Form (http://www.access-programmers.co.uk/forums/showthread.php?t=143983)
In Excel in Access (Part 1), we went from this:
http://i185.photobucket.com/albums/x317/UncleGizmo/StudentTableBoolean_2.png
To this:
http://i185.photobucket.com/albums/x317/UncleGizmo/StudentTableTransposed2.png
This was achieved with a form based tool available to DOWNLOAD here. (http://www.access-programmers.co.uk/forums/attachment.php?attachmentid=20619&d=1202664804)
See instructions on its use here: (http://www.access-programmers.co.uk/forums/showthread.php?t=143983)
However you may be looking at the new resultant table and wondering what on earth to do with it. I hope to take you through the process of making it into something useful in this thread.
The first thing you will notice is that where the check box is not checked, then that whole row is redundant, for instance there’s no need to record that ID number “1” --- “Has Not” taken Maths, English, Geography, Physics etc, it would suffice just to record the subjects that have been taken , In this case Biology, PT and Social. Looking at those entries in particular, then a general rule of logic can be defined, “delete all the rows where the check boxes are false”.
http://i185.photobucket.com/albums/x317/UncleGizmo/DataSheetForm_1b.png
Once you have deleted all of those rows,
http://i185.photobucket.com/albums/x317/UncleGizmo/DataSheetForm_1c.png
then it becomes obvious that the check boxes themselves which now “All” contain a true value are also redundant, they can be deleted just leaving you the text entry identifying the subject taken by each student.
http://i185.photobucket.com/albums/x317/UncleGizmo/DataSheetForm_1d.png
Using the “relational” properties of the database that is one more thing you can do which will improve efficiency and that is to replace each text entry --- Maths, English, Geography, Physics etc, with a number linking that field to a look up table.
First of all you need to create a look up table; this can be done by applying a create table query to extract just the unique values for the “subject” There is a video showing how to do this here: (At time index 1min) (http://www.viddler.com/explore/TonyHine/videos/67/fullscreen)
http://i185.photobucket.com/albums/x317/UncleGizmo/DataSheetForm_1e.png
This unique list should be called “tblSubject” this table is not quite finished, you need to add an identity column to the left of the text representing the individual subject, this identity will then appear in the previous table.
http://i185.photobucket.com/albums/x317/UncleGizmo/DataSheetForm_1f.png
Once you have completed the “look up table” you then need to replace the entries in the student subject table “tblStudentSubject” where it shows subject in text form with the number representing the link to the look up table. This is the query:
http://i185.photobucket.com/albums/x317/UncleGizmo/DataSheetForm_1g.png
And here is the new column created:
http://i185.photobucket.com/albums/x317/UncleGizmo/DataSheetForm_1h.png
This way your design changes to the table are making it much more efficient, holding the same information but with less data.
http://i185.photobucket.com/albums/x317/UncleGizmo/DataSheetForm_1k.png
Excel in Access (Part 1) (http://www.access-programmers.co.uk/forums/showthread.php?p=671226)
Excel in Access (Part 2) (http://www.access-programmers.co.uk/forums/showthread.php?t=143607)
Video Version HERE: (http://msaccesshintsandtips.ning.com/profiles/blog/show?id=948619%3ABlogPost%3A7031)
How to use the Normalization Form (http://www.access-programmers.co.uk/forums/showthread.php?t=143983)
Using the normalization form --- Download Here (http://www.access-programmers.co.uk/forums/attachment.php?attachmentid=20619&d=1202664804) we converted part of a spreadsheet type table into the beginnings of a relational table.
From this table we derived a “look up table” now giving us a total of three tables, the remains of the first table, “tblStudent” (the student names), the next table “tblStudentSubject” stores the subject(s) related to each student, and finally a third table, “tblSubject” a “look-up table” to store the actual subject description.
http://i185.photobucket.com/albums/x317/UncleGizmo/ThreeTables.png
From the table “tblStudentSubject” we created a form in datasheet view:
http://i185.photobucket.com/albums/x317/UncleGizmo/DataSheetForm.png
Now all we need do is combine this datasheet view form with a form based on the students list, and this will give us a form for correctly displaying the student names and the subject(S) the student is taking in one Form:
Using the wizard create a basic form from the student table and name it “frmStudent” arrange its size so it has some open space as shown.
http://i185.photobucket.com/albums/x317/UncleGizmo/StudentForm.png
Now open “frmStudent” in design view
http://i185.photobucket.com/albums/x317/UncleGizmo/DragSub_1.png
And drag the subform “sfrmlStudentSubject” into the clear area on the student form “frmStudent”
http://i185.photobucket.com/albums/x317/UncleGizmo/DragSub_2.png
You may wish to delete the text box, you don’t have to but I usually find it looks better without it.
http://i185.photobucket.com/albums/x317/UncleGizmo/DragSub_3.png
Size the form to suit
http://i185.photobucket.com/albums/x317/UncleGizmo/DragSub_4.png
Now save the form and have a look, you will notice that it incorrectly shows all of the records in the subform,
http://i185.photobucket.com/albums/x317/UncleGizmo/DragSub_5.png
Now the next bit is tricky, for two reasons, the form isn’t really on top of the other form, it actually sits in a subform window and you need to gain access to the properties of this subform window by clicking on the tiny line that you can just see around your subform. This can be a difficult task to master first time.
http://i185.photobucket.com/albums/x317/UncleGizmo/DragSub_6.png
Now open the “subform field linker” dialog box by clicking on the ellipsis (…)
http://i185.photobucket.com/albums/x317/UncleGizmo/DragSub_7.png
Phone: +44 1635 522233
Mobile: +44 7747 018875
Email: email@tonyhine.co.uk
Web: http://msaccesshintsandtips.ning.com
I have a sql server query that I need to use in access 2003 but I can not figure out how to convert the "not in" part of the query. Here is the sql server query
Select * from accounts where beg_date between '1/1/2013' and '12/31/2013' and cast(acctNo,integer) in (2,3,4,5,7,12,20) and acct_type not in ('Individual','User','Viewer')
I can not find how to do the "not in" in access sql.
I have a talbe called LOOP in access. The fields in it ar appl,sales_table,title_code,and passalong.
From this table i want to autogenerate sql and run them in a vb function through access.
My first step was crating the sql. I did it using this query.
SELECT "DOCMD.RUNSQL "&"'"&"SELECT tsr,title "&title_code&" ord_time from "&sales_table&"'"
This generates the following vb command:
DOCMD.RUNSQL "SELECT tsr,title,q2_title,ord_time from appl_sales"
The genrated sql will be a insert into query but this is just an example.
Now this will give me the sql that i want. But I have no idea how to get that into a function.
Basically say it generates 10 different vb commands i need that to run as one function. Is there a way to do this through Access and VB.
Any help would be greatly appreciated.
Thanks
Recently I am using this code to import the data in a excel sheet into Access table:
Code:
DoCmd.TransferText acImport, , strcTableName, strFullPath, True
But this will import all the data in that worksheet into the Access table.What I want to implement is to import part of the data. For example, the data I need is start from Row30 till the end, thus I don't need the data from Row1 to Row29.
i need to append data that is currently in a single MS Access table into multiple MYSQL tables that have primary keys and auto_ids etc. Therefore I need to run the queries in order so that I can use the new auto_ids correctly, see below:
INSERT INTO users (username, unconfirmed_email, password, agrees_with_terms, active, created, modified)
VALUES ("46", "", unix_timestamp(now()), 0, 0, now(), now());
SET @curr_user_id = LAST_INSERT_ID();
INSERT INTO accounts (type_id, status_id, profile_name, email, match_permissions, odesk, created, modified) VALUES (3, 0, "46", "", 1, 1, now(), now());
SET @curr_account_id = LAST_INSERT_ID();
INSERT INTO accounts_users (account_id, user_id, role_id, created, modified) VALUES (@curr_account_id, @curr_user_id, 1, now(), now());
is there a way i can change a single Access append query to generate the required insert statements to do this?
I am trying to create a button which will merge data from specific fields from the active record in my impounded dog table/form into a pre-existing word template. I have the table & form set but am only trying to merge a few of the fields in the table. Things like the Impound ID (Primary Key), Time, Date, & Location of Seizure, Dog Breed, Colour, Markings, Age, Sex, & Collar.How would I put this in a macro or is it even possible to fit in a macro?
View 1 Replies View Related