Creating Two Tables - Old Entries And Weekly New Entries?

Mar 13, 2014

I have a table in access which is updated weekly; I need to create two tables from this updated table.

1st table will consist of all the new entries for the current week

2nd table will consist of all the entries from the previous week - an amalgamation of all the entries which are not from the "current week" (table) For example; the table below shows the two entries from last week.

ID
Name

1
Adam

2
Ben

This week I have three new entries New entries

ID
Name

3
Charles

4
Richard

So when I run the same query next week I will get something like this.

Old Entries
ID
Name

1
Adam

2
Ben

3
Charles

4
Richard

[code]....

How do I get a query /queries which divides up the weeks new entries and also all the old entries.

View Replies


ADVERTISEMENT

Creating Budgets From Already Exisiting Entries?

Feb 12, 2006

Hi all,

I'm working on a database for my school that is designed to keep a record of each staff members activities that they go through during the year. With the activity information, all their expenses for that activity are listed (see database for more information). Anyway, for anyone who has the time to look at it, here is what I am having trouble putting together:

I have done everything except create budgets. I would like to be able to set a yearly budget for each department (Maths, Science etc.), with the ability to create a mini school budget (Junior School, Senior School etc.) from the sub department budgets. Laid out like this:

Budgets:
- Junior School
- Middle School
- Senior School $8050 (worked out from the total of the department budgets below)
-- Maths $2000
-- Science $3000
-- English $3050

What I would like to be able to do is get the Total Expense for each activity that each staff member has undertaken, and subtract that from that staff members working department (shown in database). I would then like to be able to create a report, showing:

Departments Budget
How much the department has spent
How much they have left in their budget

You can then use the figures for each department to get these values for an overall Senior School budget:

Mini Schools Budget
How much the Mini School has spent
How much they have left in their budget

Does anyone have any ideas as to how I could tackle this?

The database is available for download here:

http://seconddimension.com.au/Database/StaffProLearning.zip

(Hold down Shift when opening the database to access design mode)

I'll look forward to hearing from people soon.

sugar05

View 1 Replies View Related

General :: Creating Entries In A Table Based On Multivalue Lookup

Aug 3, 2014

I am designing a database for a quarry for maintenance of their machines. I have a table that has a full list of all the parts on the machines, and a multiple value lookup field that says what machines said parts are used on. I am trying to have these values translate to another table, so I can then insert a field to say how many of each part are used on the respective machine, to display on an information form for each machine.

I would also like this table to update if the relevant information is updated, for example if a new machine is input, then have the table update to reflect what parts are used on it (an After Update function?) I have made a query that gives me the read out I want (attached) but just cannot figure out how to get that into a table so I can add the extra information.

View 1 Replies View Related

Selecting From Table In Form Creating New Entries As Opposed To Using Existing

Sep 18, 2012

I have setup a simple access database consisting of 3 tables

Volunteer
Id (PK)
Name

Job
Id (PK)
JobName

Link
Id (PK)
VolunteerId (FK)
JobId (FK)

There is a one to many relationship between Volunteer and Link and a many to one between Link and Job

I want to create a form from which I'll select a Volunteer Name and then choose a JobName from a list box of options and have it create an entry in link with the id of the volunteer selected and the id of the job selected

So let's say there are 5 entries currently in the Job Table.. let's call the JobNames A,B,C,D and E.

In my form I select my name from the list (sourced from volunteer.Name) and then select B from the dropdown. My desired result would be a single entry gets created in Link with VolunteerId = 1 (representing me) and JobId = 2 representing the selection of B. Problem that I am having is that after I select B in the dropdown it creates a new record for B in Job and the Link table gets updated with that JobId as opposed to simply using the one that was already in the table.

View 1 Replies View Related

Forms :: Creating A Form Which Copy Selective Entries From Previous Record

Dec 10, 2013

I am tasked with creating a scientific database of fish catches at various different sampling stations. I envisage the entry form having hierarchy levels, ie year, season, date, sample station, species caught, and then the specific associated data with each species (weight, abundance, length etc) will form each distinct record.

In each year there are 2 sampling seasons. In each sampling season there are up to 20 dates on which samples are taken. On each date there are up to 50 sample stations recorded. And at each station up to 20 species can be recorded.

In order to enter each species caught in a survey, the end user will potentially have to fill the season box with an entry 40,000 times. For each season there will be 20,000 date entries to be made. For each date, 1,000 station code entries, and 20 species entries for each station. What I would like to happen is this.

When the end user is entering the data, he or she will be working through the hierarchy from the ground up. Ie., they will enter a particular species' data (abundance, weight, length etc) at a particular station on a particular date during a particular sampling season. The next entry will be a different species at the same station on the same date during the same season. I would like the duplicated information from the previous record to be copied across onto the new one so that all the end user has to do is enter the species name, abundance weight and length data.

Once they have finished entering all the species data for that particular site, they can move onto the next site which they would fill in the data themselves for the station code. So, in the hierarchy, the first button would copy all the previous data except the species name and associated weight length abundance data. The next button would do the same, except leave the station code blank. The next button would leave the species name, station code and date blank. And so on up the hierarchy. The end user would be able to selectively choose which data is copied over by using these buttons, saving lots of potential extra work doing repetitive entries.

View 1 Replies View Related

Creating Combobox With Unique Month And Year Entries From A Date Field In A Table

Jul 18, 2005

Hi,

Anyone know how to create a combo box that has unique month and year entries from a table (month in one column and year in another - i.e. 2 columns)?

I've been able to get the textbox of the combobox to output the correct format using a custom format but it does not affect the combobox data. Also, I'm not sure how to separate this by two columns...

Any help would be much appreciated!

View 2 Replies View Related

Find Matching Entries In Other Tables?

Dec 10, 2007

Im using Access 2000.

Essentially I want to find out which of the entries in my master table have matching entries in my other tables, and list the ones that do.

So if I have an entry in the master table for “productA”, and there are also matching entries for “productA” in tables “SupplierC” and “SupplierD”. I want to perform a query that will output a list showing “SupplierC” and “SupplierD” (I have A & B tables but if there isn’t an entry in them for “ProductA” I don’t want them on the list.)

I intend to use the results to populate the values of a combo box in future so I require the list to be in a single column, rather than across many columns. Does anyone know if this is possible?

My master table is called “OurProductsTable” and the four supplier tables are called “SupplierA”, “SupplierB”, “SupplierC”, and “SupplierD”.

Each Table has a primary key called “ProductID” and I have linked them together on the relationships screen.

I’m not sure if this is the proper method but I also made another field in each of the supplier tables called “CompanyName” and set the default value as the name of each supplier, so if the entry for supplierC matches the master table entry I can return the “CompanyName” value of “SupplierC”.

Here’s what I thought the code should kind of look like but I don’t know how to apply it properly in a query:

View 4 Replies View Related

Tables :: Lock Historical Entries?

Oct 1, 2012

I have a table of Dealers. Each dealer has a REP. I want to CHANGE the rep of the Dealer going forward but RETAIN the historical.

View 4 Replies View Related

Tables :: Create One To Many Table Entries?

May 8, 2013

I'm new to MS Access, indeed database design as a whole.

I have been tasked with creating an issues logging database and am having some issues...

I have a table/form that has all the fields I want, including a unique ID, called "Issues". I want to be able to log multiple entries in another table called "emails". The trouble is I am not having much luck. I can create an entry in the issues Form which then shows in the emails but I can't then add another email under the same ID in the "Issues" table.

View 2 Replies View Related

Tables :: Multiple Entries In One Field?

Feb 19, 2013

Let's say if I am building a database for a library and in a table the book titles are the primary key and there is a field "authors" to record the authors' names.

But sometimes a book may have several writers and if I type all of them into one field, Access only recognise as one person. So how can I format/set up so that when I use query or filter, each one of names can be identified?

If multiple entries are not feasible, how else should I design my tables?

View 4 Replies View Related

Tables :: Missing NumberID 8 In Table With 22 Entries

Dec 30, 2012

I have a table with 22 entries and some how numberID 8 is missing . How can I get the numbers back in order without redoing the whole thing?

View 2 Replies View Related

Tables :: Total Of Entries In Date Field

Jan 29, 2014

I have a simple table named [Groups] containing a Primary key ID, a Group name, a Group Leaders name and a date field. The date field merely lists the date of a particular Group related occurrence.

I need to know how many records have dates entered and assumed a simple total would achieve this. The Total is correctly shown in the appropriate row but most, but not all, of the date records are converted to show the same 'total' number.eg the date 28/01/2014 is changed to !4. Quite a weird occurrence.

Is it the case that date records cannot be totaled, in which case, why not all?

View 4 Replies View Related

Tables :: Posting Multi-entries From Drop Down

Nov 1, 2012

I have a field in which I wish to post several contacts. The contacts are listed in a table and populate the specific field via a drop down. In this specific field I want to select "mary" "bob" and "fred" (all three to display) however only one is displayed.....

View 3 Replies View Related

Queries :: Show Entries From 2 Tables Not In Third Table

Jun 22, 2013

I have 3 tables;

tblEmployees - Stores employee data
tblProcess - Stores process data
tblTraining - Stores data on which employees have trained on which process

tblEmployees has a 1 to many relationship to tblTraining through empID
tblProcess has a 1 to many relationship to tblTraining through pcsID

When a new Process is added, a query auto populates tblTraining with 1 record for each employee in tblEmployees. Equally when a new Employee is added, a query auto populates tblTraining with 1 record for each Process in tblProcess.

In theory this should ensure that every employee has a training record for every process (and vice versa). However, as many of us know, never assume the user wont find a way to mess things up!

So i want to have a query that can show me Employee and Process without a training record. I have been manipulating a SELECT (SELECT) query to pull this together, but i just keep coming up blank.

I have attached a copy of the relevant tables and query. I have deliberately deleted a training record for empID 6 on pcsID 1 AND empID102 on pcsID 2 (qryCheckTrainingRecordsBalance will show the processes these 2 DO have a training record for).

View 5 Replies View Related

Tables :: Database With Multiple Entries For A Single Date?

May 17, 2013

We have two facilities that receive four different types of shipments. I wanted to set up a database to track these shipments. Is there a way for me to set up tables such that the person entering the information can leverage the same date and location field for each entry type? For example, we may only receive two types of shipments today and four tomorrow. I want to avoid having the clerk enter the date and location two times today and four times tomorrow for each data entry.

Here's an example of what I mean above:

Date Location Type Qty
5/16 1 A 10
5/16 1 B 1
5/17 1 A 1,000
5/17 1 B 100
5/17 1 C 1
5/17 1 D 11

I'd like for the clerk to select from a fixed number of locations (1 and 2) and a fixed number of types (A, B, C, or D) and provide the date and quantity received.

View 4 Replies View Related

Tables :: Linked Table - Adding Entries To Each Row Of Data

Jul 2, 2014

I have a table that is linked to a survey and pulls data from the limesurvey backend.

The user would like to add a local field, so they can add entries to each row of data.

Is that possible ?

View 1 Replies View Related

Removing Duplicate Entries From Joined Tables In CrossTab Query?

Jun 19, 2007

Hello all,

I have made a crosstab query that is sporatically making duplicate counts. I'd like to know how to fix the problem, but more importantly - I'd like to know why and how my query is giving me these results so I can avoid making this same mistake again.

The relationship is set up so that all records from one table are returned, with only matching records from the other - so no problems that way as far as I can see. This is my crosstab query that is giving some counts of the same record as being matched sometimes two, three, or even four times:

TRANSFORM
Count([Testing DB].[DB_ID]) AS [CountOfDB_ID]

SELECT
[Testing DB].State, [Location].[North], Count([Testing DB].[DB_ID])
AS [Total Of DB_ID]

FROM [Testing DB] LEFT JOIN [Location] ON [Testing DB].[Address] = [Location].Address

WHERE (([Testing DB].[Window Length]) Is Not Null))

GROUP BY [Testing DB].State, [Location].[Size]

ORDER BY [Testing DB].State, [Location].[Size]

PIVOT Format([Date],"mmm-yyyy");

When I do a simple query for a list of the records being counted by this query, I can see the duplicate entries and can eliminate them simply by adding "Distinct" to the select statement - but I haven't had any luck adding any kind of distinction with this crosstab that will do the same.

How can I eliminate duplicate counts of records in my crosstab? If anyone has a suggestion, it would be much appreciated.

View 1 Replies View Related

Tables :: Queue With Several Concurrent Entries - Reduce Duplicate Orders

Jan 24, 2013

I'm trying to create a database that is going to be used to deliver some work to several of our users. Each time they open a specific form they'll be delivered a job.

The tables are organized somewhat like this

tblQUEUE
PK_ID_Job(AUTO_NUMBER)| JOB_NUMBER(INT)|LOCKED(BOOL)

tblWorkOrders (FK_ID_JOB (INDEXED DUPLICATES ALLOWED))
FK_ID_Job(LONG)|Name(STR)|Date(DATE)|

Now it works like this, the user gets an ID_JOB from queue

In the form they get all the all the work orders with that ID_JOB, the thing is i'm getting users with same duplicate orders cause i can't update the locked efficiently.

Regarding the users, the database is split, multi-user, with >30 simultaneously

I'm been trying to use dao.recordset, with transactions to try and reduce the duplicate orders.

View 8 Replies View Related

Modules & VBA :: Code To Consolidate Two Tables And Remove Duplicate Entries

Oct 3, 2013

I have two tables in my Access database. Table 1 has three columns, First Name, Last Name and Phone Number. Table 2 has just 2 columns, First Name and Last Name. What I would like to do is write a VBA macro to consolidate Table 2 into Table 1 and insure any duplicate First Name-Last Name combination is removed.

Table1

Code:
F.Name L.Name Tel.N.
John Smith 12345
Larry Miller 12344
Tony Woods 12333

Table2

Code:
F.Name L.Name
Albert Black
Larry Miller
John Bush

Table1 Post-Macro

Code:
F.Name L.Name Tel.N.
John Smith 12345
Larry Miller 12344
Tony Woods 12333
Albert Black
John Bush

View 1 Replies View Related

Creating Weekly Report From Cumulative Totals

Mar 13, 2013

I have an excel report which I would like to run through Access to drive trend analysis and compare with other similar reports. The excel report has a cumulative spend figure each week and not the actual weekly spend numbers, the budget figure also can change depending on the actuals.

Excel report:

Week 1
Product ID
Customer
Yearly Budget
Spend

1122
Sam
100
3

1123
John
200
4

[code]...

Will I need to create a new table each week or can I link the file and it updates automatically?Can Access store the weekly data and just update it one week at a time?

View 3 Replies View Related

How Do I See More Than 10,000 Entries?

Dec 21, 2005

I am looking into a database that has become very large (20,000+ entries) and access only allows me to look at the first 10,000. I am sure there is an easy way to check this out, but I am new to access, any help would be appreciated. Thank you.

View 2 Replies View Related

Entries Sum

Aug 2, 2007

is there a way to total up the number of rows i have in a table/query and have that number show up on a new seperate row kinda like the way sums are shown in msexcel. using access2003 btw, thanks

View 2 Replies View Related

Getting 2 New Entries In DB

Mar 29, 2006

When I try to add a topic to my database it adds the topic twice (using Internet Explorer). When I use Firefox it only adds one (as it should).

I realy don't know what's wrong

Anyone an idea?

Code:if Process="AddToDb" then'Add to DBset conn=Server.CreateObject("ADODB.Connection") conn.Open "w002399556_db_new" sql="INSERT INTO tblReview (TopicID, ReviewActiv, FieldName, ReviewTXT, Autor, Editor ) VALUES ('" & PlantID & "', True, '" & FieldName & "', '" & strMessage & "', '" & LoggedInUserID & "', '" & LoggedInUserID & "')"conn.Execute sql,recaffectedconn.closeSet conn = Nothingresponse.redirect(domain & "/db.asp?id=" & topicID)end if

View 2 Replies View Related

To Many Entries

Dec 13, 2006

hello,
I have a Database table that was pretty big, there is a little over 18k entries, so many that the form I was using won't display them all, so I had to split it into several tables, and made forms for each, now the database users want to make a mailing list out of all these different tables, they want to be able to display the entries from sertain fields with checkboxes and not others, but I can't figure out how to make a query and/or report that will look into all the different tables and pull information from all of them to make into one.
In case that wasn't clear, basicaly I have:
DB A-H
DB I-M
DB N-S
DB T-Z

and I want to make a mailing list that will look into all the tables and print only those which have the "member" field check box marked.
is there any way I can do this? do I sound stupid for not being able to figure this out? I've tried google but can't get any information that helps me, maybe I'm typing the wrong thing.
if anyone can help it will be greatly appreciated

View 3 Replies View Related

Bad Entries - Is There An Error Log?

Oct 10, 2006

I have a small access db in a network which has started to crap out far too often lately. The main table records job entries and inserts a date stamp. Every now and then, an entry has a ridicuous ID (auto inc field) and/or a bad date stamp and i can't delete the record (instead i have to output everything to ecel as best i can, reimport it to a table and reset al the fields..)

i don't know what the problem is but i'm wondering if access writest to a log file somewhere (that might help) or if there is a way to make it do this?

cheers
jg

View 3 Replies View Related

Multi Entries

Dec 5, 2004

Hi everybody

I have an assesment and need to create a database, I'm just starting with access and I need some little help if anyone has time.

I'm doing the database for an interim managment company and i have problem with the experience category. Basically because every candidate has got at least 3 or 4 different experiences, I don't know how to design the table. All I know is that I probably will have to create a separate table for this.

Thanks to anyone who will help me.

Ilan

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved