Database Design Question

I am very new to database design, and kind of learning as I go. Thus I have
a question about the design of my database.
I am working on a budget database. I have a table that contains all the
Categories (Cat_ID, Description, Income/Expense), a table with transactions
(I have other tables doing other unrelated things. I have a form to input
transactions, and a form to add new categories (This is a multiple items
form). The categories is basically my chart of accounts.
My ultimate goal is to create a form where I can select a budget month and
year, and have it list all the categories so I can input budget amounts. I
want the form to be able to adjust to an added category entered through that
category form.
I don't know SQL so if we can only use that if necessary that would be
great. Thanks in advance for your help. I have tried numerous things, but
can't get my mind around the design to do it correctly.

I would create a form that has a subform for the monthly budget. Once you
choose the month, store that date (as a date, so you want 1/1/10, not 1/10,
you can format it to look like 1/10 after the data is input)
In the subform, link on the date as a foreign key, or use a autonumber as
Primary/Foreign keys in the Form/Subform Use a combo box to display the
categories, and a text box to enter the amount.
In use, you'll add a date on the main form then start choosing categories
and adding amounts in the subform.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com
"Ray_Rattler" <RayRattler@discussions.microsoft.com> wrote in message
news:2D143DA6-1371-4C6E-92F0-023F37A84A7B@microsoft.com...
>I am very new to database design, and kind of learning as I go. Thus I
>have
> a question about the design of my database.
>
> I am working on a budget database. I have a table that contains all the
> Categories (Cat_ID, Description, Income/Expense), a table with
> transactions
> (I have other tables doing other unrelated things. I have a form to input
> transactions, and a form to add new categories (This is a multiple items
> form). The categories is basically my chart of accounts.
>
> My ultimate goal is to create a form where I can select a budget month
> and
> year, and have it list all the categories so I can input budget amounts.
> I
> want the form to be able to adjust to an added category entered through
> that
> category form.
>
> I don't know SQL so if we can only use that if necessary that would be
> great. Thanks in advance for your help. I have tried numerous things,
> but
> can't get my mind around the design to do it correctly.

0

Arvin

1/15/2010 10:09:29 PM

Thanks for your help.
So would I have a table that just has dates and is then linked to a second
table that keeps track of the date ID, category, and amount?
In the subform, could I have all the categories in the category table listed
with a box to add the amount for the month?
Thanks
"Arvin Meyer [MVP]" wrote:
> I would create a form that has a subform for the monthly budget. Once you
> choose the month, store that date (as a date, so you want 1/1/10, not 1/10,
> you can format it to look like 1/10 after the data is input)
>
> In the subform, link on the date as a foreign key, or use a autonumber as
> Primary/Foreign keys in the Form/Subform Use a combo box to display the
> categories, and a text box to enter the amount.
>
> In use, you'll add a date on the main form then start choosing categories
> and adding amounts in the subform.
> --
> Arvin Meyer, MCP, MVP
> http://www.datastrat.com
> http://www.mvps.org/access
> http://www.accessmvp.com
>
>
> "Ray_Rattler" <RayRattler@discussions.microsoft.com> wrote in message
> news:2D143DA6-1371-4C6E-92F0-023F37A84A7B@microsoft.com...
> >I am very new to database design, and kind of learning as I go. Thus I
> >have
> > a question about the design of my database.
> >
> > I am working on a budget database. I have a table that contains all the
> > Categories (Cat_ID, Description, Income/Expense), a table with
> > transactions
> > (I have other tables doing other unrelated things. I have a form to input
> > transactions, and a form to add new categories (This is a multiple items
> > form). The categories is basically my chart of accounts.
> >
> > My ultimate goal is to create a form where I can select a budget month
> > and
> > year, and have it list all the categories so I can input budget amounts.
> > I
> > want the form to be able to adjust to an added category entered through
> > that
> > category form.
> >
> > I don't know SQL so if we can only use that if necessary that would be
> > great. Thanks in advance for your help. I have tried numerous things,
> > but
> > can't get my mind around the design to do it correctly.
>
>
> .
>

0

Utf

1/15/2010 11:03:01 PM

There are a number of ways you could model this but I'd suggest the following
tables in addition to your existing ones:
BudgetYears
….BudgetYear (integer number - primary key)
BudgetMonths
….BudgetMonth (integer number - primary key)
….BudgetMonthText (text – indexed uniquely)
CategoryBudgets
….BudgetYear (integer number – part of primary key)
….BudgetMonth (integer number – part of primary key)
….Cat_ID (long integer number – part of primary key)
….BudgetAmount (currency)
In the case of the last table to make the three columns the table's composite
primary key Ctrl-click on each field, making sure you click on the field
selector (the little grey rectangle to the left of the field name), then
right-click and select 'Primary key' from the shortcut menu.
BudgetYears is simply a table of all years over the period in which you are
interested. It can be expanded in future if necessary by inserting more rows.
BudgetMonths is a table of twelve rows with values 1 to 12 in the first
column and January to December in the second.
The CategoryBudgets table models a 3-way relationship between the BudgetYears,
BudgetMonths and Categories tables. It is this table on which the form you
wish to create will be based by making its RecordSource a query which joins
this table to Categories on Cat_ID, returns all the columns from
CategoryBudgets, and is sorted first by year, then by month, then by category
description. You should be able to design this simply enough via the query
designer, but its SQL would be:
SELECT CategoryBudgets.BudgetYear,
CategoryBudgets.BudgetMonth,
CategoryBudgets.Cat_ID,
CategoryBudgets.BudgetAmount
FROM CategoryBudgets INNER JOIN Categories
ON CategoryBudgets.Cat_ID = Categories.Cat_ID
ORDER BY CategoryBudgets.BudgetYear,
CategoryBudgets.BudgetMonth,
Categories.Description;
Having created this query start off by creating a continuous forms view form
based on it. Save it as frmCategoryBudgets. You can use the form wizard to
do this. Save it as frmCategoryBudgets. You then need to amend the form
design in a number of ways:
1. Change the Cat_ID control to a combo box and set it up as follows:
RowSource: SELECT Categories.Cat_ID, Categories.Description FROM
Categories ORDER BY Categories.Description;
BoundColumn: 1
ColumnCount: 2
ColumnWidths: 0cm;8cm
If your units of measurement are imperial rather than metric Access will
automatically convert them. The important thing is that the first dimension
is zero to hide the first column and that the second is at least as wide as
the combo box.
You'll now be able to select a category by description, but the value of the
control will be the hidden Cat_ID.
2. Make sure the form has a form header and size so its deep enough it to
hold a couple of controls in which you'll select the year and month.
3. Add an unbound combo box cboYear to the header, with a RowSource property
of:
SELECT BudgetYear FROM BudgetYears ORDER BY BudgetYear;
4. Add an unbound combo box cboYear to the header, with a RowSource property
of:
SELECT BudgetMonth, BudgetMonthText FROM BudgetMonths ORDER BY BudgetMonth;
Set its other properties as follows:
BoundColumn: 1
ColumnCount: 2
ColumnWidths: 0cm;8cm
5. Open the form's RecordSource query in design view and in the 'criteria'
row of the BudgetYear column put:
Forms!frmCategoryBudgets!cboYear Or frmCategoryBudgets!cboYear Is Null
6. In the 'criteria' row of the BudgetMonth column put:
Forms!frmCategoryBudgets!cboMonth Or frmCategoryBudgets!cboMonth Is Null
Save the amended query.
7. With the form open in design view, in the AfterUpdate event procedures of
the cboYear combo box requery the form and set the default value for the year
to the selected year with:
Dim ctrl As Control
If Not IsNull(ctrl) Then
Me.BudgetYear.DefaultValue = """" & ctrl & """"
End If
Me.Requery
8. In the AfterUpdate event procedures of the cboMonth combo box requery the
form and set the default value for the month to the selected month with:
Dim ctrl As Control
If Not IsNull(ctrl) Then
Me.BudgetMonth.DefaultValue = """" & ctrl & """"
End If
Me.Requery
Save the form
When you open the form it will by default show the budgets for all
years/months. By selecting a year and month in the unbound combo boxes the
form will be restricted to the budget rows for the year and month selected,
so you can select a year and see the budget for the whole year and then a
month to restrict it further to the month in question.
Adding the budget for a new month is simply a question of inserting new rows
in the form, one for each category. If you want to automate it so that new
rows are inserted en bloc for all categories then you will have to execute an
SQL statement in code, which you can do in the Click event procedure of a
'New Budgets' button in the form header like so:
Const MESSAGETEXT = "Year and month must be selected."
Dim cmd As ADODB.Command
Dim strSQL As String
Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdText
strSQL = "INSERT INTO CategoryBudgets " & _
"(BudgetYear, BudgetMonth, Cat_ID) " & _
"SELECT " & Me.cboYear & ", " & Me.cboMonth & _
", Cat_ID FROM Categories"
cmd.CommandText = strSQL
' make sure both year and month have been selected
If IsNull(Me.cboYear) Or IsNull(Me.cboMonth) Then
MsgBox MESSAGETEXT, vbExcalamation, "Invalid Operation"
Else
' insert new rows into table
cmd.Execute
' requery form to show new rows
Me.Requery
End If
Ken Sheridan
Stafford, England
Ray_Rattler wrote:
>I am very new to database design, and kind of learning as I go. Thus I have
>a question about the design of my database.
>
>I am working on a budget database. I have a table that contains all the
>Categories (Cat_ID, Description, Income/Expense), a table with transactions
>(I have other tables doing other unrelated things. I have a form to input
>transactions, and a form to add new categories (This is a multiple items
>form). The categories is basically my chart of accounts.
>
>My ultimate goal is to create a form where I can select a budget month and
>year, and have it list all the categories so I can input budget amounts. I
>want the form to be able to adjust to an added category entered through that
>category form.
>
>I don't know SQL so if we can only use that if necessary that would be
>great. Thanks in advance for your help. I have tried numerous things, but
>can't get my mind around the design to do it correctly.
--
Message posted via http://www.accessmonster.com

0

KenSheridan

1/16/2010 12:23:07 AM

I had assumed that there was other information in the first table. If it's
just dates on the main form, you can dispense with it and add the date to
the second table, using a single form, in continuous form view.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com
"Ray_Rattler" <RayRattler@discussions.microsoft.com> wrote in message
news:1A6EC797-94ED-4764-AC57-29A0BC2F0418@microsoft.com...
> Thanks for your help.
>
> So would I have a table that just has dates and is then linked to a second
> table that keeps track of the date ID, category, and amount?
>
> In the subform, could I have all the categories in the category table
> listed
> with a box to add the amount for the month?
>
> Thanks
>
> "Arvin Meyer [MVP]" wrote:
>
>> I would create a form that has a subform for the monthly budget. Once you
>> choose the month, store that date (as a date, so you want 1/1/10, not
>> 1/10,
>> you can format it to look like 1/10 after the data is input)
>>
>> In the subform, link on the date as a foreign key, or use a autonumber as
>> Primary/Foreign keys in the Form/Subform Use a combo box to display the
>> categories, and a text box to enter the amount.
>>
>> In use, you'll add a date on the main form then start choosing categories
>> and adding amounts in the subform.
>> --
>> Arvin Meyer, MCP, MVP
>> http://www.datastrat.com
>> http://www.mvps.org/access
>> http://www.accessmvp.com
>>
>>
>> "Ray_Rattler" <RayRattler@discussions.microsoft.com> wrote in message
>> news:2D143DA6-1371-4C6E-92F0-023F37A84A7B@microsoft.com...
>> >I am very new to database design, and kind of learning as I go. Thus I
>> >have
>> > a question about the design of my database.
>> >
>> > I am working on a budget database. I have a table that contains all
>> > the
>> > Categories (Cat_ID, Description, Income/Expense), a table with
>> > transactions
>> > (I have other tables doing other unrelated things. I have a form to
>> > input
>> > transactions, and a form to add new categories (This is a multiple
>> > items
>> > form). The categories is basically my chart of accounts.
>> >
>> > My ultimate goal is to create a form where I can select a budget month
>> > and
>> > year, and have it list all the categories so I can input budget
>> > amounts.
>> > I
>> > want the form to be able to adjust to an added category entered through
>> > that
>> > category form.
>> >
>> > I don't know SQL so if we can only use that if necessary that would be
>> > great. Thanks in advance for your help. I have tried numerous things,
>> > but
>> > can't get my mind around the design to do it correctly.
>>
>>
>> .
>>

0

Arvin

1/16/2010 9:08:53 PM

"Ray_Rattler" <RayRattler@discussions.microsoft.com> escreveu na mensagem
news:1A6EC797-94ED-4764-AC57-29A0BC2F0418@microsoft.com...
> Thanks for your help.
>
> So would I have a table that just has dates and is then linked to a second
> table that keeps track of the date ID, category, and amount?
>
> In the subform, could I have all the categories in the category table
> listed
> with a box to add the amount for the month?
>
> Thanks
>
> "Arvin Meyer [MVP]" wrote:
>
>> I would create a form that has a subform for the monthly budget. Once you
>> choose the month, store that date (as a date, so you want 1/1/10, not
>> 1/10,
>> you can format it to look like 1/10 after the data is input)
>>
>> In the subform, link on the date as a foreign key, or use a autonumber as
>> Primary/Foreign keys in the Form/Subform Use a combo box to display the
>> categories, and a text box to enter the amount.
>>
>> In use, you'll add a date on the main form then start choosing categories
>> and adding amounts in the subform.
>> --
>> Arvin Meyer, MCP, MVP
>> http://www.datastrat.com
>> http://www.mvps.org/access
>> http://www.accessmvp.com
>>
>>
>> "Ray_Rattler" <RayRattler@discussions.microsoft.com> wrote in message
>> news:2D143DA6-1371-4C6E-92F0-023F37A84A7B@microsoft.com...
>> >I am very new to database design, and kind of learning as I go. Thus I
>> >have
>> > a question about the design of my database.
>> >
>> > I am working on a budget database. I have a table that contains all
>> > the
>> > Categories (Cat_ID, Description, Income/Expense), a table with
>> > transactions
>> > (I have other tables doing other unrelated things. I have a form to
>> > input
>> > transactions, and a form to add new categories (This is a multiple
>> > items
>> > form). The categories is basically my chart of accounts.
>> >
>> > My ultimate goal is to create a form where I can select a budget month
>> > and
>> > year, and have it list all the categories so I can input budget
>> > amounts.
>> > I
>> > want the form to be able to adjust to an added category entered through
>> > that
>> > category form.
>> >
>> > I don't know SQL so if we can only use that if necessary that would be
>> > great. Thanks in advance for your help. I have tried numerous things,
>> > but
>> > can't get my mind around the design to do it correctly.
>>
>>
>> .
>>

0

Wesley

1/17/2010 3:45:32 AM

Wesley, Just an idle question. At least a couple of your posts simply copy
and repost an existing post without either answering, or asking another
question, or adding anything to it? If you are doing this purposely, could
you explain why? It does, unnecessarily clutter the message thread.
Larry Linson
Microsoft Office Access MVP
"Wesley Silveira" <wesley_silveira1990@hotmail.com> wrote in message
news:C22EA0C8-6F01-4C19-9055-AAE2893C9DA3@microsoft.com...
>
> "Ray_Rattler" <RayRattler@discussions.microsoft.com> escreveu na mensagem
> news:1A6EC797-94ED-4764-AC57-29A0BC2F0418@microsoft.com...
>> Thanks for your help.
>>
>> So would I have a table that just has dates and is then linked to a
>> second
>> table that keeps track of the date ID, category, and amount?
>>
>> In the subform, could I have all the categories in the category table
>> listed
>> with a box to add the amount for the month?
>>
>> Thanks
>>
>> "Arvin Meyer [MVP]" wrote:
>>
>>> I would create a form that has a subform for the monthly budget. Once
>>> you
>>> choose the month, store that date (as a date, so you want 1/1/10, not
>>> 1/10,
>>> you can format it to look like 1/10 after the data is input)
>>>
>>> In the subform, link on the date as a foreign key, or use a autonumber
>>> as
>>> Primary/Foreign keys in the Form/Subform Use a combo box to display the
>>> categories, and a text box to enter the amount.
>>>
>>> In use, you'll add a date on the main form then start choosing
>>> categories
>>> and adding amounts in the subform.
>>> --
>>> Arvin Meyer, MCP, MVP
>>> http://www.datastrat.com
>>> http://www.mvps.org/access
>>> http://www.accessmvp.com
>>>
>>>
>>> "Ray_Rattler" <RayRattler@discussions.microsoft.com> wrote in message
>>> news:2D143DA6-1371-4C6E-92F0-023F37A84A7B@microsoft.com...
>>> >I am very new to database design, and kind of learning as I go. Thus I
>>> >have
>>> > a question about the design of my database.
>>> >
>>> > I am working on a budget database. I have a table that contains all
>>> > the
>>> > Categories (Cat_ID, Description, Income/Expense), a table with
>>> > transactions
>>> > (I have other tables doing other unrelated things. I have a form to
>>> > input
>>> > transactions, and a form to add new categories (This is a multiple
>>> > items
>>> > form). The categories is basically my chart of accounts.
>>> >
>>> > My ultimate goal is to create a form where I can select a budget
>>> > month
>>> > and
>>> > year, and have it list all the categories so I can input budget
>>> > amounts.
>>> > I
>>> > want the form to be able to adjust to an added category entered
>>> > through
>>> > that
>>> > category form.
>>> >
>>> > I don't know SQL so if we can only use that if necessary that would be
>>> > great. Thanks in advance for your help. I have tried numerous
>>> > things,
>>> > but
>>> > can't get my mind around the design to do it correctly.
>>>
>>>
>>> .
>>>
>

0

Larry

1/17/2010 8:30:43 PM

Reply:

Similar Artilces:

Access Database Conversion to Excel DatabaseI need to convert a downloaded database in Access format
to an Excel format I can use on my desktop.
Hi Greg
One way
Use Data>Import External Data in the menubar
--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)
www.rondebruin.nl
"greg" <anonymous@discussions.microsoft.com> wrote in message news:b0f301c3ec2b$2b12d830$a001280a@phx.gbl...
> I need to convert a downloaded database in Access format
> to an Excel format I can use on my desktop.
...

Nothing shows up in Database objects in Database WizardHello, I am having touble with Visio 2003 once again. Though the db wizard,
I created an excel spreadsheet (table) for data and am tryinig to link the
chart back to this original database so that when I change something in the
database it will change correspondingly. I am going back through the DB
wizard, and can only get as far as selecting a database object to connect to.
I have selected the original dada source, checked table, but nothing showes
up under the database objects. Am I or my computer missing something?
Please help!
Thank You.
Have you defined a name for the region ...

Simple VBA QuestionI'm new at VBA, so this should be an easy question for
you. What VBA method/statement would I use to bring data
from one excel spreadsheet to another.
Example:
I have a worksheet (C:\ TempReport.xls)
On the open event of the current worksheet I want to copy
the entire worksheet in TempReport and bring it over to my
current worksheet.
I know how to do this on by creating a macro, but is there
a way to do this without opening the user interface and
copying and pasting? A way that does it all in the
background?
Thanks.
One way:
Private Sub Workbook_Open()
Const SOUR...

Newbie question on Lookup errorUsing Excel 2003.
A2 = L
A3 = M
A4 = H
B2 = 1
B3 = 0
B4 = -1
A2 though B4 named as Table
Data is in column D, starting at D2
I am using =Lookup(D2,Table), D3 etc.
Data returs properly in D2 and D3 but in D4 I get the dreaded #N/A no
matter how I change the structure and data.
Any help appreciated.
Thanks,
James
What's in D2, D3, and D4?
My guess is that the error is occurring due to the lookup range not being in
alphabetical order.
You my try a combonation of Indirect and Match as an alternative.
=INDIRECT("B"&MATCH(D2,$A$2:$A$4,0)+1)
HTH,
Paul
--
"Dasin&...

Huge databaseGood afternoon, i have to make a huge database wich have to get data from
excel file.That is not a big problem problem is that i have to get
information for about 1000 families with 1000 elements for every family and
this database have to storage this information at leaset one year. This make
a huge pack of elements. Can Microsoft Access get so huge pack of data ? If
yes i would be glad to read some ideas.
Thanks
If you are talking about 1000 columns in a table, Access can't do it. 255 is
the upper limit.
1,000 * 1,000 is potentially a million records. However if the data is
pr...

Designate a MMkt fund as the associated cash account?I opened a new Fidelity brokerage account to receive some stocks and
mutual funds being transferred from another account. In the
transferred-in account the cash was kept in a money market fund, but
when I download the information on the new account from Fidelity, the
cash from the money market fund showed up in the "associated cash"
account, and the money market fund does not appear at all.
Is there a way to designate the MMkt fund as the "associated cash
account," or is there a better way to handle this situation?
In microsoft.public.money, Don Gatz wrote:
>I opened...

linking 2 databases in 1Hi,
I am running 2 hqs for 2 different purposes, now the company decided to use
1 hq for both purpose.
How can i link the 2 databases into 1 database????
I tired to link them from SQL server enterprise manager-all task- export
data from database A to B but some tables can not be exported and show me
tables failure,
is it possible to link them or im just trying something impossible?
Advice me on that plZzzzZZzz
--
Aliko
...

Newbie Update Query Question?Access 2007
I have an equipment inventory in Access.
I have a separate lookup table in an Excel spreadsheet that looks up
barcode numbers and creates a daily list of equipment on a site.
Can I use an Update Query to change the "Location" field in the main
inventory list in the database?
If so, do the records from the spreadsheet have be identical field-for-
field as the main database inventory?
Example:
Database Inventory:
Barcode Number, Item Type, Make, Model, Description, Serial, Purchase
Price, Location
Excel List
Barcode Number, Description, Location
The Excel list mi...

Importing Current Database into CRM DatabaseHi,
I would just like to know if it is possible to tranfer my current database
into the Adventure Works Database(and if so how) or is it used just for a
few records for testing purposes.
You help will be highly appreciated...
there is a redeployment tool on support.microsoft.com/downloads under
microsoft crm. This may help you.
--
John O'Donnell
Microsoft CRM MVP
http://www.mscrmfaq.us
"Rock619" <rodger@intervigil.com> wrote in message
news:5728edd40161d2cca1c0e079cfd86665@localhost.talkaboutsoftware.com...
> Hi,
>
> I would just like to know if it is pos...

how to start a database
hi guys, i want to start a database on excel 2000, i never had to do
this before, and i know this can be done.
i just dont know where to start from !
what i want to do is, it is a racing club, i want a listing of all
pilot and a sheet where we can enter the number of the car(all pilot
info need will be transfer) for final result of the race.(right now
they enter the car no, name , city at every race at every registration,
if he is register to 3 race they do the process 3 time.)
like in access went we create the database and after the query, that
what i want to do in excel.
any tip or link...

Plot Area Sizing QuestionThis is really a beginner question I am sure. Would really appreciate some
help. When I create a chart in Excel 2003, sometimes the plot area is too
small and crowded and not all my data labels show up. Is there any way
other than dragging the corners to make the chart bigger (which doesn't
always work) to make it big enough for all my labels to show up? THANKS!!
You can format the x-axis:
Click on the x-axis to select it
Choose Format>Selected Axis
On the Scale tab, for Number of categories between tick-mark labels, enter 1
On the Font tab, you can choose a smaller font si...

Budget questionI have a recurring bill set up for $69.47 for my Cellular service. This
shows up correctly in the budget planner. But when I go to the "Review you
current budget status" page, it says I'm over budget because I only have
$59.99 budgeted, which I think is the original amount I created the bill
for. What's going on? I'm now running M04, but I just upgraded from M02,
and this was an issue then too.
This might depend on when you made the change to the budget entry. If you
made the change this month, i.e. whilst the budget month was running, the
budget entry will take ef...

Impact of changing database column width?Hi,
We are a software product company and are planning to begin using the
Contract Administration module. To do so, we will be switching our inventory
items from Track: None to Track: Serial Numbers and we will be storing our
software license keys in the Serial Number field which is column SERLTNUM in
table SOP10201.
Unfortunately, SERLTNUM is CHAR(21) and our license keys are a few
characters wider than 21. We can get the keys down to 21 characters by
removing three embedded hyphens, but there are several databases outside of
GP where the license keys must still exist with...

Use query from another databaseHi
In the code below is there a way that qryOrderHistory could be in a
separate database. If so how would i refer to it in the code.
Thanks
Dim db As DAO.Database
Dim qd As DAO.Querydef
Dim rs As DAO.Recordset
Set db = CurrentDb
Set qd = db.QueryDefs("qryOrderHistory")
qd.Parameters("prmCustNo") = lngCustNo
Set rs = qd.OpenRecordset()
If Not rs.Eof Then
.... code to populate an excel spreadsheet
Change your 'Set db = CurrentDb' line to something like the following ..
Set db = DbEngine.OpenDatabase("full path and name of target MDB here")
--
...

Database Queries with database views, that base on many database tI created 2 database views. Each of them uses 256 database tables.
From every database table only one column is used in the select list of the
view. Every database table has one column which is used for the join.
Each database table has 5 rows. The database tables have not any foreign
keys and indexes.
Both views works fine. When I use the database views in a query like this:
SELECT v1.*, v2.* FROM v1, v2 WHERE v1.id=v2.id
I should get 5 rows with 512 columns in the resultset.
But I get the following error from the SQL-Server (after about 5 minutes):
Msg 8621, Level 17, State 2...

Set Database in ExcelIn Microsoft article 183446 under method 2, it refers to using "Set Database"
on the Data menu. I am using Excel 2002 (the article says it applies to
Excel 2002), but Set Database does not appear in my Data menu. Is this an
error?
It's an error due, I think, to very old menu structures. The article says
that the information applies to everything from version 5.0 (1995) through
2003, inclusive. But I believe that the Set Database menu item disappeared
from the Data menu as of Excel 97. And one has long defined a name using the
Insert menu, not the Formula menu. But my rapidl...

MSAccess 2000 Newbie Question...Hello everyone,
I have almost never touched Access before, and I think I have to use it
for what I would like to do; it is this:
I have weekly pay data .csv files with 14 fields, including employee ID
number, pay code, shift, pay rate, etc. The .csv files are named by
pay period end date (e.g., 20060507; 20060604). Using a date range, I
would like to be able to compile reports about overtime hours worked
and paid, or hours by shift, etc. This kind of thing is easy enough
for me in Excel on a week by week basis, but I get stumped at how to
compile either annual data, or selected range data...

Finance Charge Question #2Here actually is the Finance Charge Question but the other AR question
is still valid.
I have a customer that wants to charge a finance charge per document.
They are a property management company that sends out one bill per
month. Their finance charges are fixed amounts. If they already
charged a finance charge for a given month (or bill), they don't want
to charge it again for that month when they assess finance charges the
next month. Any ideas? third party solutions?
--
Japheth Nolt
Microsoft SBF Specialist
Landis Computer
www.landiscomputer.com
2/28/2007 4:21:06 PM
Japheth,
I do...

upgraded from MSSQL 2000 to MSSQL 2005 now database's users haveHello.
I updated the MSSQL 2000 server to MSSQL 2005 server now the database's
users have no
login names. I found a way to list orphaned users sp_change_users_login
'Report' but it only listed dbo and there are actually five listed with
MSSQL Server Management Studio Express. I tried this sp_change_users_login
'update_one', 'RPS', 'RPS' but I get the following error the user name 'RPS'
is absent or invalid.
Any ideas?
Try with Auto_Fix instead; see the example at the end of the following
article:
http://msdn.microsoft.com/en-us/...

Client access problem after databases restorationHello!
Recently we had a problem with an Exchange 2003 Back-End server with 6
mailbox store distributed thru 4 SG, and we lost the databases because the
SAN's disks partitions were deleted by mistake.
So, we had to do a restore from a backup, but when we could not mount the
stores, so we used eseutil /p command, and after, eseutil /d, and then
isinteg -s servername -fix -test alltests. We could mount the stores, users
can use email.
The problem now is that we have some users that could not access their
mailbox thru Outlook, and when we try to move to another store, we receive
an ...

database or other software?I need help with the following situation:
I want to create files or a database that will contain information about my
clients, with their business information (i.e address, phone, fax, type of
business, in which states they do business) to send an invitation to bid on a
construction project. I want , where a database would fill out the
information automatically on the bid invitation, then fax, or email the
invitation to each selected clients according to their specialty. Then I will
need to keep track of who answers my invitations in order to "weed out"
clients who do...

Form in another databaseHow can I check if an Access form is open or loaded when the form is in
another Access application (mdb or nde file).
Alex
...

Heps to design Locked/Unlocked cells in protected worksheetDownload it from http://www.addintools.com
The Cell Lens Of Locked&Unlocked render and change the background color of
cells according to their lock/unlock state: the locked cells to gray, the
unlocked cells to blue. Assist has also offered a Quick Lock button to lock
the selected range at once and a Quick Unlock button to unlock immediately.
Now you are very clear about which cells are locked and which cells are
unlocked. Just cancel this Cell Lens, the background color will be restored.
Cell Lens Of Data Type render and change the background color of cells
according to their data ...

Recover a Prod content database to Dev farmHello,
I was wondering is it possible to recover (copy) a content database from a
seperate farm to a Dev farm? I came across a few hints about using the "Copy
the database files to a network folder" option, but nothing concrete. I'd
appreciate if someone could clarify this for me?
regards,
Edgar
You can recovery a database to a network location. We have the following
options:
http://technet.microsoft.com/en-us/library/bb808956.aspx
You will see the details in the "Tasks" section
--
Santhosh Sivarajan | MCTS, MCSE (W2K3/W2K/NT4), MCSA (W2K3/W2K...

Attendance Database SetupI am creating an attendance database for my company.
There are 19 different departments in the company and each manager
tallies
his/her own employee attendances.
I had a spreadsheet set up, but it wasn't on a rolling calendar basis.
Then,
I was going to have the managers delete old months, but that would most
likely mess with the formulas.
So the new spreadsheet needs to be on a rolling calendar basis, so that
when
a new month comes up, the previous month from last year will drop off
in
calculations.
My file has three worksheets, one titled "Summary", one titled
"Detail...