How To Manage A Notes Table With A Weekly Imported Main Table

Mar 6, 2013

I have a main table that is imported weekly from another Access DB which I have no control of. I also have a new table with a notes field and a product ID field. The issue is the product ID field in the main table is constantly growing. When I created a query with all of the fields from the main table and the notes field from the notes table I could not enter any data into the notes field unless the product ID was already listed in the notes table. Is there a way to make a query update the notes table or a macro to add the missing product IDs from the main table to the notes table?

View Replies


ADVERTISEMENT

General :: Unbound Main Form - Manage Items In A List Box

Feb 20, 2014

I have a list box on an unbound main form, which contains a rowsource consisting of files in a certain folder. The listbox is unbound

when I change an item in a subform, the listbox should update to show different items from the same folder.

Now it is updating correctly, so the rowsource appears to be correct, , but then the listbox behaves strangely - with the first item being sort of permanently selected - or at any rate - strange selection behaviour

out of interest, changed it to a combo box and it works correctly. so there must be some difference between the two?

After investigation, it might be this : [URL] ....

The appearance is similar to what is described in the thread.

although I have played with the strings to get them shorter without getting it work correctly. very strange

if I run the code to update the listbox from the subform, either directly, or by running as sub IN the main form, it produces this strange behaviour. If I run exactly the same code directly IN the main form, it seems to work properly.

View 14 Replies View Related

Queries :: Save Weekly Changes To A Table

Nov 18, 2013

We import data to a table on a weekly basis. We delete the table and replace the data. Most of the data is the same. It's date's, group names, ...

Now we would like to save the weekly table and add a saved date. Then we would like a query that puts all the saved tables together and that we could filter.

For example. A team is planned to do a work on 01/01/13. The next week it has changed to 07/01/13. So the data in the table it updated but we can't track the changed date. If we had the different tables we could filter that team and so that the work was originaly planned on date x to be done on the 01/01/13, on date y it was 07/01/13....

Of course if we would save the complete table every time it would mean a lot of useless data that hasn't changed. So a better solution would be that a query could see which data has changed and it only saved those lines on that date.

View 11 Replies View Related

Imported Table Not Included In Reports!

Sep 2, 2004

I have an imported table that I created a form for (containing 400+ entries). I created a subform on it for a new table that I will be entering information to tie in with the imported form.

However, when I try to create a report, the report won't acknowledge any of the information. It's all outlined in the SQL, but nothing appears.

I did discover, though, that if I create a new entry on the imported table, that information appears on the report, but nothing for the existing data that was imported.

Please help!!!!!

View 1 Replies View Related

General :: Staff Table - Calculating Average Weekly Hours

Mar 4, 2015

I have a table with staff in.

I have a table with the start and end of their shifts.

We have four possible locations and four weeks.

So I have four tables for each location.

I have a module that can work out hours and deductions based on time in and out. I built that into a seperate databse working on a one time in and one time out setup.

I want to incorporate the two so I can get the hours worked over four weeks at all locations and divide that by 4 to get the average weekly hours for a staff member.

View 8 Replies View Related

Remove Quote Marks From Imported Table

Mar 16, 2008

I am using DoCmd.TransferText to import a CSV file to a new table.
The problem is some of the fields contain quotation marks which really messes things up for me later on.
Is there some way I can go through the whole table and remove them?

Maybe a Query?
Or VBA?

View 9 Replies View Related

Excluding Deleted Records From An Imported Table

Jun 6, 2007

Hi

I have a table that I am importing from FOXPRO DATABASE. When someone goes into the system and deletes a record, Foxpro does not really delete the records but marks it for deletion. There is no field to query on that I can see in the table, but when I go to Foxpro the record is "marked" for deletion.

Anyway to exclude these records when doing a query or importing the table?

Thanks

View 1 Replies View Related

Link Table From Lotus Notes

Oct 25, 2005

hi,

i'm using access 2000 to create an application. i need to get the information of Staff, but the Staff informationis in Lotus Notes 6.

my problem is how to link table from Lotus notes document?

an for your information exporting from Lotus Notes then access 2000 link table to that exported file is not an option.

anyone help?

regards,
askaccess :confused:

View 2 Replies View Related

Tables :: Add A Notes Section To Table?

May 1, 2013

I have made a table for prospective clients and want to add a notes section where I can add notes when I call them or they call me with the date. I have a comment section but thats not quite what I want. I would like them to be separated by date.

View 6 Replies View Related

Verify Imported Field Based On List In Table

May 15, 2006

Hi, I'm not sure if this is possible, but I've heard you guys really know your stuff.

I have a client who would like to import a list of e-mail addresses, but would like to verify the domain name used in the provided address against a list of domain names pre-populated in a table. Do I need to use Dlookup? Here are some additional details. Thanks in advance!

MAIN_IMPORT_TBL
ID
FNAME
LNAME
E-MAIL
PHONE

DOMAIN_TBL
DOMAIN_ID
DOMAIN_NAME

AP

View 8 Replies View Related

Tables :: Pull Field From Main Table And Create A New Table With Date Stamp

Nov 4, 2014

I have a make-table query that pulls all the fields from 1 table (MainTable), and creates a new table with a date stamp based apon a form value entered (New Table = MainTableWithDate).

Currently, I setup the query to pull info from the form field like this:

DateField: [Forms]![frmmain]![DateField]

However, when the make-table query is done - all date fields are blank (all other fields are correctly created), and when I look at the new created table (mainTableWIthDate), the typeassigned to the date field is "Binary" (in the form, I've specified LongDate).

View 6 Replies View Related

Queries :: Updating Child Table List View On Change Of Main Table Row

Apr 19, 2013

Here's a query that the bottom listview in the attached form i.e. a listview representing a table of calls(many) to fims (1 top listview)

Code:
SELECT calls.id, calls.firm_id, calls.called, calls.said, calls.spoke_to, calls.next
FROM calls
WHERE (((calls.firm_id)=[firms].[id]))
ORDER BY calls.called DESC , calls.next DESC;

When I run the thing...I get a dialog asking me for firm id.

I want to change this so when I move up and down the firms LV (top)... the bottom LV updates taking firm id from the top LV with focus.

Access 2003.

View 2 Replies View Related

Modules & VBA :: Copy Record To History Table And Then Delete It From Main Table

Jul 9, 2014

I have a form with a sub form. when a record is choosen in a combo box the sub form is filled out with a record.

what I am trying to do is have a button that will copy that record to a history table then delete it off the the main table.

I cheated by using the wizard to get the code to delete the record but I am having troubles modifying the code to copy that record to the history table. Here is the code below. I have tried to insert code in several places but it just errors out.

'------------------------------------------------------------
' Master_tbl_sub_fm
'
'------------------------------------------------------------
Function Master_tbl_sub_fm()
On Error GoTo Master_tbl_sub_fm_Err
With CodeContextObject
On Error Resume Next

[Code] ....

View 8 Replies View Related

Records Existing In Main Table Not Found In Temp Table

Apr 11, 2007

Hoping someone can help me with this DELETE query. I have a Main table that's being updated by a Temp table that's an exact copy of the Main table but with a subset of records.

1) Insert records from Temp table NOT found in the Main table - this query I have worked out below - not tested, but the results look correct.

Need Help Here...
2) Delete Records from the Main that are not found in Temp table with an exception...only DELETE records where certain key fields are matching. i.e. If S.CAD_NAME, lngStoreNumber are a match to what's in the Main table. While
Temp table:
lngStoreNumber - CAD_NAME - lngcomponentSerial
1 - "CHK" - a
1 - "STK" - a
2 - "CHK" - a

Main table
lngStoreNumber - CAD_NAME - lngcomponentSerial
1 - "CHK" - a - LEAVE (EXISTS In Both Tables)
1 - "CHK" - b - DELETE (lngStoreNumber & CAD_NAME composite Found /lngcomponentSerial NOT Found in Temp)
1 - "STK" - a - LEAVE (EXISTS In Both Tables)
1 - "RMM" - a - LEAVE (lngStoreNumber & CAD_NAME NOT Found in Temp)
2 - "STK" - a - LEAVE (lngStoreNumber & CAD_NAME NOT Found in Temp)
2 - "CHK" - b - DELETE (lngStoreNumber & CAD_NAME composite Found/lngcomponentSerial NOT Found in Temp)
3 - "CHK" - a - LEAVE (lngStoreNumber = 3 Not in Temp table Subset)

Rule: Only delete the records for a particular CAD_NAME and lngStoreNumber from the Main table leaving all other CAD_NAME/lngStoreNumbers.

I'm running these updates in batches of lngStoreNumber. So the Temp table will only contain subsets of what's to be deleted from the Main table thus the need to link on the key fields only NOT to delete a Subset of lngStoreNumber/CAD_NAME. I think I've tried every possible query that doesn't work.

Here is query #1 to insert records missing from the Main table that exist in the Temp table. I think what I need is a variation of this???
SELECT D.*
FROM Main AS S RIGHT JOIN Temp AS D ON (S.CAD_NAME=D.CAD_NAME) AND (S.lngcomponentSerial=D.lngcomponentSerial) AND (S.lngStoreNumber=D.lngStoreNumber)
WHERE S.lngcomponentSerial is null AND S.CAD_NAME is null AND S.lngStoreNumber is null;

THANKS.

View 2 Replies View Related

Queries :: Delete Main Table Row Depending On Sub-table Row If It Is Null

Dec 25, 2014

i have two data tables, one is depending on the other. now i need to delete the main table row depending on the subtable row if it is null.

View 3 Replies View Related

Forms :: Enter Details In Main Table And Related Sub Table

May 5, 2013

I have a table TO-det and another table DO-DET.The table DO-det will have details about all DO for each TOID record.Both have a common field name TOID The tables are related under ONE-MANY relationship.One TO-DET record can have many DO-DET record

Now I wanted to create a form where if i add a new record to TOID i must also be able to add data for DO-DET for that corresponding TOID.

View 1 Replies View Related

Tables :: Import Excel Into Table / Overwrite Data Of Fields Imported

Mar 17, 2015

I have a table, which contains many fields, around 90. Out of these 90 Fields, around 40 will be imported from an Excel sheet, which has same headers as the 40 field names in my table, in which they have to get copied.

So when I import data, it copies them as new records, instead what I am looking at is, the records in Excel and in Access table has a Unique Key, so when I import data, matching with this unique key, the fields should be overwritten into those records from Excel, instead of adding new records.

E.g:

Say I have 2 records in my access:

ID(auto generated) Fab_name Issue_ID Location Remarks Data_1 Data_2 Data_3
1 Fab1 193 NY Bug OC DC MC
2 Fab2 641 DRS Error AC KC FC

Now when I am importing data from Excel with the following records:

Fab_name Issue_ID Location Remarks
Fab1 193 NY - GRM Solved
Fab2 641 DRS - SGP Forwarded

So when I import the above data into my Access from Excel, based on Issue ID, it should update the values of different fields that are imported from Excel, leaving other fields (data_1, Data_2, Data_3) as it is.

And I need this to be written as a macro, so that user just has to choose the file, which he has to import.

View 6 Replies View Related

Automatic Name Of Field When A Range Or Column Is Imported From Excel Into A Table In Access

Oct 9, 2014

When I import a column from Excel into a new table in Access using the Docmd.transferspreadsheet function, Access automatically assigns a field name of "F1"; and if I try to import a range of columns (Eg A1:B10), then Access saves them into a table with 2 columns with field names "F1" and "F2". The table is created as part of the Docmd function, and so I don't have control over the field names.

Is this going to always be the case? Can I depend on Access to always use this naming convention? Because I am about to start writing more code that will depend on these field names.

View 6 Replies View Related

Autofill Data To A Main Table From A Reference Table

Mar 19, 2007

First of all apologies for the lack of proper terminology I'm a novice Ms Access user and I like to thank everyone in advance for trying to help.

Ok here is the situation:

I have two tables, NewJobs and Contacts which have the following fields.

Newjobs
--------
JobID (AutoNumber, Primary Key)
JobName
JobDate
JobDescription
JobOwner (Linked to table 'contacts' via LookUp)

Contacts
---------
DisplayName
EmailAddress
Department
Extension

Ok basically what I want is to have a form based on table NewJobs which will allow me to enter new jobs into the database. When I get to JobOwner a drop down list linked to 'Contacts' table will show me all the data from column 'DisplayName' and allow me to select it (saves time on typing). I have already done this and its not a problem.

Now I would also like in the same form to have additional fields from table 'contacts' such as EmailAddress, Department and Extension which will autofill with the right information soon after I select a JobOwner from the drop down list.

So for example if I select 'Joe Bloggs' Access will automatically fill the additional fields in the form with Joe's information (department, extension etc) from the Contacts table.

I hope all this makes sense. Thank you all for your support.

- Mitch

View 3 Replies View Related

Creating Archive Table For 30 Days To Contain All Data Imported From Multiple Text Files?

Oct 30, 2014

i'm in the process of creating an Access database to import data in text files and then export the data as fixed width text files, this is now working fine

My next step is to be able to setup an "Archive" Table which will hold all the data i have imported across multiple import proceudres for the last 30 days, i then want to be able to "De-Dupe" any files i import against this to ensure i never load duplicate data.

View 1 Replies View Related

Removing A Subset From A Main Table

Aug 2, 2007

I have 3 parcel tables. Table 1 contains the parcels of the whole county. Table 2 contains open space parcels and table 3 contains preserved farmlands. I would like to remove parcels in tables 2 and 3 from table 1. Tables 2 and 3 are subsets of table 1.

Basically I have these:

Table 1
PIN attribute1 attribute2 .....attribute6
1
2
3
.
.
.
52,000

Table 2
PIN
5
8
9
12
18
335
789
.
.
.
N

Table 3
PIN
41
51
66
3
78
903
1245
.
.
N

Note that tables 2 and 3 only contain PINs
Does anyone have a script for this problem?
I would appreciate your help on this. Thank you.


Jun

View 7 Replies View Related

Main Table Linked To Several Sub-tables - Help!

Sep 2, 2007

Dear All

Since last week I am working on a Database which I will use for my company. To make a long story short, I didn't find any appropiate softwares nor examples on the internet, hence, with my some knowledge in Access, I am trying to make this "to be" useful tool for my self.

Short brief on the project:
This is going to be used for a trading company to register:
- Suppliers
-- Products

- Customers
-- Inquiries
-- Orders
-- Offers

*Relations:
- Suppliers can have one or many products
- Customers can have one or many Orders / Inquiries / Offers

Problems:
(I have tried many different ways, but going nowhere)

1) The "Customers" table have 3 tables (Orders/Inquiries/Offers) linked to it - and all are based on the primary key "CustomerId". --> How can I make a form to enter these data linked?

2) Under "Orders" I want to select supplier from the Supplier table and Product from the Product table -- How?

I know this is a lot of info and probably too vague, but if someone could assist me with a few problems - then I would really really appreciate it and compensate somehow.

Looking forward for your help.

Best regards

View 1 Replies View Related

Multiple Tables From One Main Table

Apr 22, 2013

I have a big number of sales staff..and broken them into different teams.

I have one TABLE1 with all their names, i need to have table which links to TABLE 1

All the fields are the same except team field. Therefore i need table 2 to only show me all fields and TEAM ALPHA only.

In total i got 46 teams therefore it is big.

View 1 Replies View Related

Lookup Table Not Working In My Main Form

Jul 14, 2006

I have my main form which is connected to my main table - Query 1

I have a lookup table (actually a view from SQL Server) which holds my user name - the username used to login to the database- just one value.

In my main form I have a 'Logged By' field. I want this field to default to my login name when I process a record.

I thought it would be as easy as connecting the lookup table to my main form and then in default value typing My_login.user_name.

Apparently not. It just has the #Name? in there.

Anyone have an opinion on this ?

I can use the =environ("username") in the default value which works to good effect but different users use each others PC and I would like the field to be defaulted to thier database login and not thier windows on.

Please help ! It seems so small and its driving me ceeerazy !

View 1 Replies View Related

Good Or Bad Idea? Text Box In Main Table Or Other?

Dec 1, 2004

I would like to add a textbox to my main form so that users can enter in some extra data regarding the job they are working on. This text box should tie to the current record of the main database, tableJobLog.

Is it best to keep this textbox as a field in the main database, tableJobLog? Or should I create a separate table (perhaps tableJobNotes) with just this text field and link the two tables?

I would think that having it in the main table would make the table grow considerably in size after some time.

If creating a separate table is better, I would need some guidance on how to do this.

Thank you very much.

View 2 Replies View Related

Modules & VBA :: Linking Subdatasheets To Main Table

Oct 29, 2013

I have a main table and multiple other tables that I want to link to each row of the main table.The main table "Data" consists of columns (Name, x, y) where "Name" is the primary key and all values are unique.Each of the other tables have columns (Name2, z) where the value of "Name2" is the same in each row and also corresponds to the table name.

I want to make each table a subdatasheet of "Data" where each row in "Data" shows the values in the table corresponding to its name. (i.e. where Name = Name2).Below is what I have so far, but the code doesn't work because linkchildfields and linkmasterfields need to be run from a subform.(?)

The error I am getting is 'Property Not Found' once the code reached the linkchildfields line.

Quote:

Sub STS()
Dim i As TableDef
Dim db As Database
Dim tbl As TableDef
Set db = CurrentDb()
Set tbl = db.TableDefs("Data")

[code]....

View 9 Replies View Related







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