access db setup and query help

[mercmannick]

Need somehow of making database save history of querys , and put week number against them.

So I can pull the data back to here from MSQuery. (Which I can do) :) about all I can LOL

the Excel side I can sort out after, (Unless Someone shows me best way before hand)

the Access side I am having great difficulty.
i can update the table imf non moves from source no problem. (but again dont know how to add week number) i will post code i use in next thread.

what i am trying to do is save the query data evertime it is run in access to history table Query.
then everyweek pull the data into here to create a series of charts

hope this makes sense

attached is both the excel and .mdb files

Merc :doh::doh:

[mercmannick]

Sub Excel_To_Access()
' exports data from the active worksheet to a table in an Access database
' this procedure must be edited before use
Dim Cn As ADODB.Connection, Rs As ADODB.Recordset, r As Long
' connect to the Access database

Dim MyConn
Dim sSQL As String

sSQL = "DELETE IMF_NoMove.* FROM IMF_NoMove;"
' connect to the Access database
' and clear contents of Filt IMF Machining NoMove
' note different syntax allowing the delete SQL to run at start of procedure

Set Cn = New ADODB.Connection
MyConn = "C:\Copy of No Moves IMF.mdb"

With Cn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.Open MyConn
.Execute sSQL
End With
Sheets("Filt IMF Machining NoMove").Activate
' open a recordset
Set Rs = New ADODB.Recordset
Rs.Open "IMF_NoMove", Cn, adOpenKeyset, adLockOptimistic, adCmdTable
' all records in a table
r = 2 ' the start row in the worksheet
Do While Len(Range("A" & r).Formula) > 0
' repeat until first empty cell in column A
With Rs
.AddNew ' create a new record
' add values to each field in the record
.Fields("Order") = Range("A" & r).Value
.Fields("Material") = Range("B" & r).Value
.Fields("Material Description") = Range("C" & r).Value
.Fields("Phase Sch Start Date") = Range("D" & r).Value
.Fields("Last Mvd Date") = Range("E" & r).Value
.Fields("DaysSLM") = Range("F" & r).Value
.Fields("Order Sch Fin Date") = Range("G" & r).Value
.Fields("Cycl Time") = Range("H" & r).Value
.Fields("Prj") = Range("I" & r).Value
.Fields("Process Order Status's") = Range("J" & r).Value
.Fields("Acty") = Range("K" & r).Value
.Fields("Work Centre") = Range("L" & r).Value
.Fields("Outs Phs") = Range("M" & r).Value
.Fields("Phase Status's") = Range("N" & r).Value
.Fields("MRP Cont") = Range("O" & r).Value
.Fields("Order Cnf Start Date") = Range("P" & r).Value
.Fields("Order Cnf Rel Date") = Range("Q" & r).Value
.Fields("Stg") = Range("R" & r).Value
' add more fields if necessary...
.Update ' stores the new record
End With
r = r + 1 ' next row
Loop
Rs.Close
Set Rs = Nothing
Cn.Close
Set Cn = Nothing
Call Data

End Sub

This is the code i use to update Access

as you can see it deletes from table before update i need it to do this but i also need it too update to history table

Merc

[OBP]

Can I ask why you are inputting the data in to Excel and then transferring it to Access when you can input directly in to Access?
Why not just repeat your code to put the data in the "history" table and just add a field input with format("ww",Date()) which gives the week no of the date that you run the query.

[mercmannick]

OBP

the data comes from a MRP system, i just open in excel to upload to acees by preference.

as once i have this dbase setup correctly i will have about another 15 or so with diffrent data , but with the intention of copying how this is set up to set the others up.

so i am open to any suggestions to setting up best way

what i need after thinking is not to keep the data history but just the Query history with the relevant week No.

then what i was hoping for is to pull back a rolling 52 week period (i know i will only have couple of weeks to start with but this will be ultimate plan)
into excel to create a series of charts by weekno from these querys

each will be by Cell : IE: Cell A , Cell B ETC , ETC


Hope this helps

Merc

[OBP]

Is the Excel sheet the output from Access for graphing or the input to Access for storing?
If this is the output to be graphed then Access "Cross Tab" queries will produce that output and it can be graphed in Access as well.
What I don't understand is the mix and match of Excel and Access, is it because you know how to do it Excel but not in Access?
Another thing I can't quite see is how you get from your code in post #1 to the data in the "Query History table" in Access and the Excel sheet DB1, what are the 14, 25,43 etc and what are the "Counts that go in the columns?

[mercmannick]

OBP

count is of how many rows by cell.
excel sheet will be used for graphing only
code i havent touched and not tryed yet with the history side i just use this to update data to Access

excel graphs compared to access graphs is the reason done in excel

Hope this makes sense

Merc

[OBP]

Sorry,from the data in Access I do not see how you get in Excel for instance
week 27 for 2005 cell E column "14" you have a count of 309.
on the next row week 28 you have a count of 298.
Where do those numbers come from?

OK, I think I might see it, is it from the queries? 14 to 24 counting daysSLM ?

Although they do not give values like those in the Excel lists.

[mercmannick]

OBP

ignore other data its old what i was trying to show was where i wanted data from Query to go

allways start at row 10 and increase with everyweek.
but set the charts for 52 rows

and have the query put the weekno next to relevant row (sorry i should of deleted old week no's)

that way i was thinking i would allways get 52 weeks data Rolling on

If there is a better way as i said before i sm open to try and make this first one right , so i can create the others

the only thing that will have to stay will be the no moves table , also i know im getting ahead of myself now but is there a possibilty of creating a form in
DB so if some one wanted to look up a week number against a specific cell , they could see through the form

Many Thanks

Merc

[OBP]

The last part is the easy bit, the form is very easy.
Which date do you want your week no based on?
I have the format method, I just need to know the date?

[mercmannick]

i will be updating this every monday OBP but our week numbers in work are from Saturdays to Fridays
the excel setup i am up for changing to the best way to suit what i am trying to do

the only thing i will need to keep is the no move table

Thanks For this Help

Merc

[OBP]

OK, in your query for 14 to 24 I have added a field called weekno.
The weekno field title has "field:Date()" less the inverted comas and in the field's property under format enter ww.
That will give you a field with the week number that you run the query for each record in the14 to 24 query.
I have attached your database with the query in it. I have also put it in the Append query, but I have not set it to append to anything.
The smily should not be there it should be a : and a D.

[mercmannick]

OBP

cool thanks , shall i create the other 3 querys the same as 14-24
or are you doing this

Many thnks

Merc

[OBP]

As long as you are happy with doing it you should carry on.
Give me a shout when you have the data in the table and then we can create the form to search for the week numbers.

[mercmannick]

OBP

i have added required fields, and got the append data to add to queryHistory when you run it

will there be a way to automate this when it is setup

Many Thanks

Merc

[OBP]

What is it you want to automate exactly?
The running of the query(s)?
Do you want the search form now, if so can you post your latst version?

[mercmannick]

yes OBP
the running of the querys , when i update the data

Thanks

Merc

[OBP]

I think that zip file is youroriginal one.
Where do you update the data from?
Excel?
It would obviously be better if you imported it directly in to Access and the you could do the query run at the same time.

[mercmannick]

oops sorry OBP

Wrong file

how do you mean import direct to Access ?

the file from MRP is an .xls

Merc

[mercmannick]

OBP

Do you mean run from access to import ,then run querys and then output to excel to do charts ?

is that possible , the file is on a networkdrive with a funny name like

rhkpi00004560wk30.xls, which will change everyweek (byweeknumber)
and there would be 2 sheets

1.IMFprocessnomoves
2.IMFmachiningnomoves
which go into same table (NoMoves)
Merc

[OBP]

The difference is that at the moment you have Excel code that pushes the values in to Access.
Access imports Excel data straight in to a table, this can be achieved in vba which would then run your query.
I have been working for the last couple of weeks with Violette Le Phan on here on doing exactly that with an import form that actually matches up the field titles in Excel with the filed titles in Access to import and append the correct data. She has produced some very impressive routines, (with a little help from her friends as they say).

[OBP]

You do not need to output the queries to Excel, you just need to "Link" to them, whenever the Excel Graphs are opened it opens the query and gets the latest data.

[mercmannick]

OBP

that sounds cool if i could get something like that going

Merc

[mercmannick]

OBP

Bear in mind i am a relative nebie to Access, will all this be easy to setup , once i have seen how one is setup i find it easy to see what is going on and be able to learn from that, and work on from there

Merc

[OBP]

Here is phase one with the Week Selection form. It didn't work out quite as I expected because although the week number field in the table is a number field it actually saves the date????
The query can't match a numberwit the date and won't convet it back.
So to get it to work I added a date field as well to the table and I use that to make the selection. There are 2 queries and 2 forms, one query/form to select the week and the second is a sub form on the first form to display the data.
There is VBA behind the Combo box that updates te date field and requeries the subform.
We can look at the other things over the weekend or next week, but I will need to know what the raw data looks like from your 2 networked files.

[mercmannick]

the actual data is in the no move table both excel sheets into same table if that helps OBP

Thanks

Merc

[mercmannick]

cool
looking ok so far

Merc