Simple database form

M

morlan

Registered User.
Local time
Today, 07:04
Joined
Apr 23, 2003
Messages
143
Hi all!

I have a really simple database with a small amount of information.

Each entry is a sales transaction made by Tom, Sue or Ben on a given day, at a given venue.

The database table looks like this:

SalesExec --------- Venue --------- Date
===============================
Tom ---------------- NewYork -------- 020103
Ben ---------------- NewYork -------- 020103
Tom ---------------- NewYork -------- 020103
Tom ---------------- NewYork -------- 030103
Sue ---------------- Texas ---------- 040103
Sue ---------------- Texas ---------- 040103
Ben ---------------- Texas ---------- 040103
Tom ---------------- NewYork -------- 050103
Sue ---------------- Texas ---------- 050103
Tom ---------------- NewYork -------- 060103



OK, So I want to ask the database the following questions.

How many transactions did Tom make on 020103?
How many transactions did Tom make in total?
How many transactions were there in Texas on 040103?

At the moment I can get the database to count the total amount of transactions made by each person but not by person and date.
I am using this formula =Sum([SalesExec]="Sue") and this will tell me how many Sue did in total.

Ideally I would like to do this in a form and have dropdown boxes for each person and venue and then a text field for the date. The answer would just appear in a box

Can some one help me here.. I know these are relatively simple fomulas and I am really keen to figure out how it all works :)

Thanks
 
We've moved from Ireland to America now? :cool:

Here, I threw this together - you'll obviously need to put a bit more work into it.

And here's a link to the original thread.
 

Attachments

Last edited:
Mile-O-Phile said:
We've moved from Ireland to America now? :cool:

Here, I threw this together - you'll obviously need to put a bit more work into it.

And here's a link to the original thread.
Click to expand...

Hey thats fantastic.. I appreciate the help :). It will help me understand how it works.
Just one question though - you have 3 seperate tables in the database. Is it possible to have all the information in just one table as I will be importing large speadsheet files into the database every day? Thanks again mate.. dont know what made me change country!!
 
You could have it all in one table for importing but here's my reason:

I made three separate tables because it's a better database design than to have all the information in one table.

This split takes the database to what is called: 2nd Normal Form whereby the table is already in 1st Normal Form (for each row-by-column there should exist only one value) and every column that is non-key is fully dependant upon the primary key.

As an aside, 3rd Normal Form is where the table is in 2nd Normal Form and all of its non-key columns are mutually independant (meaning are not dependant upon values in other columns i.e storing a field called Total where the total is dependant upon two other fields called Price and Quantity, and the Total is the Price multiplied by Quantity does not make a table 3rd Normal Form.)

Another reason for the split of the tables is that if you have one table and you list every employee's name within one column, what happens when a female employee gets married and her name changes and then you decide to count all her sales over the year when she got married last month (you'd only get her sales for the month) unless you went through the table changing her name which can be time consuming when you would only have to change it once in the separate table.

As for the spreadsheet part: I'm sorry about that - maybe someone else can offer some ideas.
 
I'd be askin a bit too much of you but could you do an example, same as the last one, but with all info in just one table? I been trying to do it myself for the past hour and I just cant get it to work. Sorry for takin up your time.. i appreciate the help!!
 
Is it just those three columns that you get from the Excel spreadsheet or is there more.

I'm thinking that, rather than violate accepted database design, you can add a new sheet to your spreadsheet that you'd export to the database where this sheet uses Lookup formulas to get the data exact and can then be exported straight into a sales table.
 
Hi Duck,

I have attached a sample of the speadsheet that I recieve every day. In reality it contains about 40 columns of information but for the database we only need information from the first 6 columns.

From this sample spreadsheet I would like to just import it into the access database and then the database form (just like to form you compiled) could querie the table. I think there only needs to be one table because the database will never get that big and the information in it will never change.

ObjectID - The unique ID of the object, this could be used as the primary key.
IsCompleted: Indicates if the sale was completed or cancelled (not important)
SalesExec : The sales person
Venue: The location of the sale
Pitch: The detailed location of the sale (not important)
DateTimeCreated: The date and time the sale was created, the time does not matter, can a fomula ignore the time and just get the date?

So just like in your form, you could ask the database, How many sales has John done today, or how many sales has Venue made this month. That type of thing..

I've learnt a good few things from looking at your formulas, thanks!

Do this make things any clearer?
Whats the facination with ducks? :p
 

Attachments

I obviously cannot stress how much you should NOT have it as one table.

Have a look at the spreadsheet I've re-attached and look at my suggested method.

As for the fascination with ducks...it all started here.
 

Attachments

Surely throwing ducks in an offence?

Thanks for doing the spread sheet, it works like a gem,
I have added some more sale executives to the list but now some fields in the export array are showing up as #N/A. I have double checked the formulas / read the help and understand them but it's not working . I have re-attached the sheet? It has stopped me dead in my tracks.

Also, in the export array, is there a way to get the formulas in each row to increment automatically? for example, in column C:

=VLOOKUP(BaseData!C14,Employees,5,FALSE)
=VLOOKUP(BaseData!C15,Employees,5,FALSE)
=VLOOKUP(BaseData!C16,Employees,5,FALSE)
=VLOOKUP(BaseData!C17,Employees,5,FALSE)
=VLOOKUP(BaseData!C18,Employees,5,FALSE)
=VLOOKUP(BaseData!C19,Employees,5,FALSE)
...and so on

The spreadsheet will have up to 1000 rows in it!!

BTW Where did you learn all this?
Cheers m8!
 

Attachments

Once you have all the data in - you have to redefine the range in Excel.

Do this by going to:

Insert -> Name -> Define

Select the employees range and redefine it to include all the new data.

You'll get one person who hasn't had their name put in the employees range - best to add them to :)


You've added employees and inadvertently added spaces to the end of some of their names.

On the formula that makes their full name, change the formula to:

=CONCATENATE(TRIM(B2)," ", TRIM(C2))

and just drag it down for all the names.

As for the VLOOKUP formulas, when all the data is in the BaseData sheet, select the last row of the six columns in the sheet Export and drag down until all formulas match data on the BaseData sheet.



And regarding where I learnt all this - I'm self taught.
 
OK Duck,

Thats worked an absolute treat. Now I have a cleaned up version of the spreadsheet.

I have also updated the DB for the SalesExecutives area. You might want to take a look.
Off for the weekend to do some reading. :) thanks for the help so far!!
 

Attachments

I don't know why, but I laughed when I saw Peebles was now a location. :p

Is any more help needed?
 
Mile-O-Phile said:
I don't know why, but I laughed when I saw Peebles was now a location. :p

Is any more help needed?
Click to expand...

Yeah Peebles is a strange name but believe it!! :D

I have been doing I bit of work over the weekend.. I learnt a few things. Heres what I've done so far (attached)


Now what I want to do Is to be able to ask the database the following..

How many Sales did Bristol to today? This week?

Ideally a viewable & printable report that would look like this:


Sales in Bristol
==============================

This week:

SaleExec ======== M ==== T ==== W === T ==== F
_____________________________________________

Debbie Cole ====== 20 === 20 === 20 === 20 === 10
Hone Woodman === 10 === 10 === 20 === 10 === 20
Jeremy Simmons === 10 === 10 === 20 === 20 === 10
Susan Kelsey ===== 10 === 10 === 20 === 10 === 20
Neil Jowett ====== 20 === 10 === 10 === 20 === 20

Total sales ======= 90 === 60 === 70 === 70 === 80

Total sales this week: 370


So I need it to filter SalesExecs from Bristol between 14 - 18 April and do a count. Is this complicated? ?
 

Attachments

morlan said:
Yeah Peebles is a strange name but believe it!! :D
Click to expand...

I know it!

To produce that sort of report I believe you are going to have to use a CrossTab Query.

I've never actually used them myself so I can't help much there, I'm afraid.
 
Mile-O-Phile said:


I know it!

To produce that sort of report I believe you are going to have to use a CrossTab Query.

I've never actually used them myself so I can't help much there, I'm afraid.
Click to expand...

OK no probs, forget about the date thing :o :) . how do I get it to count sales by site? ie. How many sales in Bristol today?

ps. Do you know of anyone here on the forum that might know how to use the crossTab querie to count sales by week?

Cheers
 
On a form, make a combobo with the SiteID and the Site as its two columns - hide the SiteID column (set its width to 0).

On the combo's AfterUpdate() event, select the code builder and put the line: Me.Requery

Make a query based on your sales table with the SiteID field having its criteria as the combobox on your form using the [Forms]![frmYourForm]![cboYourCombo] syntax.

Have a textbox on your form with its ControlSource being: =Count([AFieldInYourQuery]).

That should do it.
 
Mile-O-Phile said:
That should do it. [/B]
Click to expand...

Yes ... I created a combo with 2 columns, set one column to 0 width. I set the AfterUpdate() event and selected the code builder and put the line: Me.Requery.


Make a query based on your sales table with the SiteID field having its criteria as the combobox on your form using the [Forms]![frmYourForm]![cboYourCombo] syntax.
Click to expand...
:confused: :(
 

Attachments

Sorry Duck, I cant get it to convert to Access 97 :o
Remember you first example dbGrrr, do you think you could do an example in that one? But seriously, you dont have to :) Ive learnt a good bit already, thanks
 
You must log in or register to reply here.

Similar threads

C
Date Filter not working
Replies
3
Views
537
Gasman
Gasman
P
Building a MAUI App...
Replies
1
Views
444
Isaac
Isaac
J
Solved Count blank detail sections in report to minus from grand total
Replies
1
Views
376
Jomat
J
C
AfterUpdate double amount
Replies
2
Views
385
Gasman
Gasman
A
Simple warehouse data entry
Replies
12
Views
672
arnelgp
arnelgp

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom