I have my Data Types sent to "Date/Time" and a format YYYY/MM/DD.
What is a good method of handling approximate dates? IE: Early April 2012, or ~5/14/2012, or TBD.
Eventually, I want to create queries to calculate dates against each other.
I would like to see if I am doing this okay. I want to confirm the best manner of storing a BilltoID and a ShiptoId address.
I presently store 1,000 BilltoID's with 1 address each record in table1. Then I have 1,500 ShiptoID's with 1 address each record in table2.
I figure that i would make a main form with a pulldown combo box on the left for soldtoID and have an address travel in from table1. and then a different set to the right with another pulldown combo box for the shipto address pouring in from table 2.
I have looked at some models. they seem to have the BilltoID address is linked to a table1 like mine with one address in a combo box. However the Shipto address section in their samples is a Manual input section and not a table.
So is my method better? That is, using 2 tables and 2 combo boxes on the form.
Unrelated to my Order form, i want to mention that I also have a Contact table with many people who could get letters from us. so i have more address over in that table using 2 fields in this contact table (billtoID and shiptoID). therefore, this Contact table can be used for a subform and connect to a main form using those 2 fields as parent child thing.
thanks you for reading this and giving suggestions before i proceed.
I have a database split into a frontend and backend.
The front end has several make-table queries that create tables of data on which pivot tables and other analyses queries are based. The make-table queries get re-run every now and then as new data get entered.
The resulting tables are huge - 500K records in some cases. It seems like a bad idea to store them in either the front and back end.
Should I create a second "backend" that holds just these temporary tables? If so, what's the best way to design the make-table queries so they make the table in this "backend"?
Hello,
I am building a Db that will report against individual transactions. Each transaction will be on a specific day.
Now I will need to slice and dice the data in a number of different ways depending on what the end user wants to see.
Therefore I need to build a date table that will aggregate all the individual days by Week, Month, calendar year, fiscal year...etc.
Being a rookie, I was simply going to build a table that has all the days for the last three years and then the corresponding weeks, months etc in columns to the right.
Because of this design, I will have over 1,000 records. Could I do it differently to keep the size of the table down?
For example use ranges as a cell value (Our week 1 is named "Feb 1st Wk", it is 1/30/06-2/5/06 for fiscal Yr 2006, 1/31/04-2/6/05 for fiscal Yr 2005...etc)
Any advice on this?
Thanks, Paul
I am trying to format an excel spreadsheet through access, specifically trying to convert a column from text to dates (I receive the data in text format and need to translate it to dates).
Code:
Dim excelApp As Object
Set excelApp = CreateObject("Excel.Application")
excelApp.screenupdating = False
excelApp.Visible = False
Set excelWB = excelApp.workbooks.Open("Z:DataBasicSMData.xlsx")
[Code] ....
I'm no longer getting an error, but it isn't actually modifying the spreadsheet...
hello,
I am just wondering whether there is any way I could define a query in such a way to include a record in Table A if field FA is approximately equal to field FB in Table B by no more than, say 12.
Thanks!
Regards,
Anyi
I have a form that shows a customer number and a dollar amount. In a subform, I want to show all the records with that customer number that are "close" to the dollar amount. For this purpose close can be INT(amt1) = INT(amt2). But I can't figure out how to put that in the parent/child relationship on the sub-form.
Any help?
Thanks,
David
Can somebody point me in the direction of a good website to find out about how temporary tables are used?
We have one that picks up all fields in table except one and I have no idea how the information is gathered or where it comes from.
Thanks.
Hi There,
Is it poss to create a temp variable in a query. I want its value to be some text then a field:
"some text " + [name]
How do I create this in the query?
Thanks,
I would like to create a "global" temp table that can be viewed by all users. The syntax is:
Create [TEMPORARY] TABLE tablename (field type)
QUESTION1:
When I put the optional [TEMPORARY] in, I get a syntax error...
Create [TEMPORARY] TABLE t (ID int)
I've tried...
Create TABLE [t] (ID int)
Is that the same as doing this???
Create TABLE t (ID int)
Can someone clear up my syntax?
QUESTION2:
If i can create a temp table when does the table go away? When I close access? Do I have to delete it?
Can someone clear this up? Thanks.
Hello eveyone.. I have a slight problem which goes way over my knowledge of the subject!
I have to make a form saying "ISLAND HORSE RACING CLUB" appear for 5 seconds before loading up the main form to add records but I cannot for the life of me figure out how to do it!!
anyone got any pointers? had a look at "timed events" on this forum but dont know how to implement it to help me
Any help would be appreciated
Regards
AaRrGgHh
hi
hi
i have sql query in a string
strQuery="...."
how to create temporary table with the result of this query?
thanks
Hi there
I'm new to this so please be patient!
I have developed an Access database which produces financial reports based on a date range which the user specifys, taking the data from a SQL server. He chooses his dates and then runs a macro which creates the new tables, overwriting previous tables. Numerous financial reports run on these tables so I need to keep the table names the same.
The problem is that only one user can access the DB at anytime because the new user can't delete the other users table becuase it may be based on a different date range.
I'm open to suggestions but maybe what I need to so is create tables which are based on the user id when he logs in to the Access database so that he doesn't delete someone elses table. How can I do this please!!!???
Thanks in advance
Davebhoy
For my inventory DB, when creating a purchase order, I need to store all transaction in a temporary folder and give a temporary number to PO and after finalisation only all data should be appended to main po table with new po number that will be last+1. And all data from temporary tables should be deleted. Like an air ticket is generated, first checks all details, give seat location also and if "Committed" gives the final ticket with no
Any idea about structural configuration and type of queries to be used
Hey again.
Asked a question last time that might've been a little too complicated but I've made a lot of progress since then. :)
I've a form which I enter weekly data into which I have transferring into a temporary table. At the bottom of this form I've got a button which runs an update query which takes the entries from the temporary table and adds them to the main table.
(I'm doing a fantasy football league. An example is that if I enter that a player has recieved one yellow card in the weekly data, when I update the main table it will add one yellow card onto the total, etc.)
The problem I need help with is that I find that I need to get the temporary table to wipe itself as soon as I press the button to update the main table. Is there a way I can work it into the update query, or will it need to be done seperately?
Thanks.
(I have searched for this kind of thing, and found a topic that was dealing with VBA. From what was posted it didn't seem to relate to my problem, so I decided a new topic would be my best bet to get some help.)
Hopefully someone can point me in the right direction.
I have a small spare parts db which also has pictures of some of the parts.
manufacturer
partnumber
wholesale
retail
picture
The form carries data on wholesale price which I would need to hide if the customer would like to see a picture of the required spare part.
Is it possible to have a button to temporary make the wholesale field non-visible? Or would it be better to click on a button to open a new page with just the picture of the selected part and the retail price, if so what is the best way to achieve this.
Thank you in advance for any assistance.
Kim
I am using different values of a record (selected from a combobox) in my form/table which a user can edit and afterwards use for calculations and reports. the problem I encounter is that the fields are bound so if changed there is no undo if not mistaken.Is there a way to have for instance load a record in a temporary place (without making a large amount of temporary variables) and only update if the "save" button is pressed?
View 1 Replies View RelatedI'm thinking about creating temporary (while db open) relationships between tables, since transfering tables is possible only if no linked relationships are present. However it's important to preserve in some cases referential integrity.Looking online, I found this code:
Code:
Public Function CreateRelation(primaryTableName As String, _
primaryFieldName As String, foreignTableName As String, _
foreignFieldName As String) As Boolean
[code]...
Even with the comments I don't really understand what exactly this code does or doesn't do.I tried the code. It seems (?) to create a relationship. Since the relationship doesn't show up in the relationships table, I'm not really sure if it's there.
- why it's not visible among other relashionships (or is it me?? maybe, it wasn't the code that was working but the query...)
- how to implement referential integrity
- what's the behaviour of this supposed relationship. Is it permanent or not? If not when is it deleted?
I'm trying to put values into a temporary table for the user to view the work behind a returned calculated value.
Here is my code:
Code:
Sub SearchPartNumber_Entered()
Dim txtPartNumber As Variant
Dim rst As Recordset
Dim rstt As Recordset
Dim u As Variant
[code].....
How do I put all values under all 'u' 's into one table?
I want to store a query into a table, which I will delete later on. But somehow it shows me an error: Data type conversion error at the qdf = CreateTableDef assignment line.
Code:
Public Sub LF_Query()
Dim i As Integer
Dim strSQL As String
Dim qdf As TableDef
[Code] .....
I'm making a library database program thing... There's an option for the user to view all books on loan.
I have two tables:
Books, which has columns ID*, ISBN, Author, Title, Year, Location
BorrowerStorage, which has columns Book ID, Name, Email Address, Desk Number
Book ID in BorrowerStorage is related to the Books primary key.
Now, for the viewing all books on loan, I want it to produce a read only table which contains all the entries from the BorrowerStorage table and the corresponding Title/Author columns (i.e. the records for which the ID in Books column = BookID in Borrower Storage column)...
I'm trying to create a temp table, which is populated by a string, (taken from a recordset).
My problem is incorporating the String into the SQL statment, and making it work,
What I'm trying to do is to create a temp table, and populate it with the first record of the recordset, (which is an e-mail address).
(The recordset and the strings work fine). It's the SQL statement which doesn't work.
Here's the code I have :
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~
Set DBS = CurrentDb
Set RST = DBS.OpenRecordset("SELECT Contact FROM Contacts_to_be_Mailed")
Dim My_Count As Long
Dim ContactString As String
RST.MoveFirst
ContactString = RST(0)
Dim strTable As String
strTable = "TempContact"
DoCmd.RunSQL "INSERT * INTO " & strTable & " FROM ContactString "
My setup:
frmUsedOilContract (contains a header and a subform)
subfrmUsedOilContract (contains a few controls) [datasheet view]
- Removed Date
- Voucher Number
- Building Number
I implemented some code so that a temporary default value could be set for the date and the Voucher Number. I also have the default value for 'txtRemovalDate' set at 'Date()'.
PHP Code:
Private Sub txtVoucherNumber_AfterUpdate() 'Set current value to default value.
txtVoucherNumber.DefaultValue = txtVoucherNumber.ValueEnd Sub
PHP Code:
Private Sub txtRemovalDate_AfterUpdate() 'Set current value to default value.
txtRemovalDate.DefaultValue = txtRemovalDate.ValueEnd Sub
Also have code so the default value is null when the form is opened.
PHP Code:
Private Sub Form_Open(Cancel As Integer) 'Set Default Value properties to nothing.
txtVoucherNumber.DefaultValue = vbNullString txtRemovalDate.DefaultValue = vbNullStringEnd Sub
This all works great until I try to change the date. If I use the default date (today's date), the new record will stay with today's date. If I change it to a different date, the new record displays a time instead.
I'm trying to insert 10% of a dataset from dbo_billing into another table Random_Temp. Another form is open when this query is to be ran that passess in the billyear and billmonth... I'm sure it's a syntax issue as I can isolate the random number part and it displays the appropriate data, I just can't re-write it to insert into the other table:
INSERT INTO Random_Temp ( indx, peopleId, audited )
SELECT TOP 10 PERCENT b.indx, b.peopleId, b.audited
FROM dbo_Billing AS b
WHERE (((b.billYear)=[Forms]![billing]![billyear]) AND ((b.billMonth)=[Forms]![billing]![billmonth]) AND ((b.recertifying)=-1))
ORDER BY Rnd(-(1000*b.indx)*Time());
I have been trying to create a login form that allows the user to change his/her temporary password logging it to the proper table along with timestamp and who done it info.But, after spending the morning trying to find the proper syntax I am flummoxed.
I can get everything to work accept the update of the fields. I can get the command to work (writes to the location) but it does the pop-up what is the parameter thing when it works. I have all the information just need to get it in so the command recognizes it.
DoCmd.SetWarnings False
DoCmd.RunSQL "UPDATE lut_TeamList SET Pass = txt_Password.value WHERE TeamListID = Me.cbo_UserName.Value"
DoCmd.RunSQL "UPDATE lut_TeamList SET UpdatedBy = Me.cbo_UserName.Value WHERE TeamListID = Me.cbo_UserName.Value"
DoCmd.RunSQL "UPDATE lut_TeamList SET UpdatedWhen = Now() WHERE TeamListID = Me.cbo_UserName.Value
DoCmd.SetWarnings True
I have recently upsized an Access database to SQL Server. The .adp is for the most part working fine.
However, when an end user tries to create a duplicate record in one of the Primary key columns of a Form he gets a SQL Server error message as follows:
"Violation of Primary Key constraint. Cannot insert duplicate key in object myTable".
This is obviously not a very useful error message. What would be the best way to ensure that a more useful error message is returned?
I have considered adding code to the "After_Update" event of the cell in question. This however seems long winded. Is there an easier option?
Thanks
Kabir