Best way of extracting data. (1 Viewer)

[kacey8]

Hi everyone.

I have managed to get my access DB set up, get the data entry, searching and editing working perfectly (in forms)

The next stage is extracting data from these.

ie I four of the columns I have are

Date Received
Portal (drop down list of 3 choices)
User Initial (list of 4 choices)
Days of the Week (ie Monday, Tuesday etc)

I need to be able to search through the database, ie between X Date and Y Date and mark the totals from each of the sections. so the following

Date: 01/06/2014
to
Date: 13/06/2014

Portals 1 = XXXX
Portal 2 = XXXX
Portal 3 = XXXX

Total = XXXX

Then I'll need to do one for all of the above. and more.

So how can I do this? will a report do it? or a form where it searches down and displays totals? I have no idea where to start.

 

[Cotty42]

Setting up a report grouped by Portal will allow you to extract this data and get totals for each portal.

Hope this Helps

Dave

 

[JHB]

Use a (sum) query with cafeterias for the between X Date and Y Date and base a report on that query.

 

[kacey8]

Sorry, maybe I am being stupid. but "Cafeterias"?


I was looking at a report and grouping it by portals, whilst it gave me a list, it didn't give me the totals.

 

[kacey8]

Okay, so I got the report between dates sorted.

I am using the following.

with two "date boxes" and a run button.

Private Sub Run_Report_Click()
DoCmd.OpenReport ReportName:="Portals", View:=acViewPreview, _
WhereCondition:="[Date Received] Between #" & Format(Me.txtFrom, "mm\/dd\/yyyy") & _
"# And #" & Format(Me.txtTo, "mm\/dd\/yyyy") & "#"
End Sub

I also have another box for a similar report which needs to filter by user ID. How can I adjust the code above so it will sort by date (like above) but also which user ID is selected in the combo box.

So I have a "User Initials" field and a combo box called "usrID" which I want to filter by.

 

[kacey8]

Okay, little bit more help please. I have a report which reports back with "Viewings Made"

Some are NULL (or empty values) How do I remove these results?

DoCmd.OpenReport ReportName:="Viewings Report", View:=acViewPreview, _
WhereCondition:="[Date Received] Between #" & Format(Me.txtFrom, "mm\/dd\/yyyy") & _
"# And #" & Format(Me.txtTo, "mm\/dd\/yyyy") & "#"

Is my current code, I assume it is a Where [fieldname] is not null
?

 

[JHB]

Sorry, maybe I am being stupid. but "Cafeterias"?

Sorry - I don't know from where that word was coming, it should be Criterias.

 

[sumox]

i think kacey8 you're totally confusing.

request one thing and get it solved. then request another then get
it solved. MIXING all the things will confuse viewers.

First of all you wish to Get Sum of all Portal Values within date Range.

So, Create a Query by wizard with date field, Portal and all necessary fields.
and instruct the Wizard to Sum the Currency/Number field i.e portal or whichever
field u want to be summed. Now goto Design view of Query.
Note: there shud be only 1 date field.

Now, Type there in Date field criteria
"Between [Enter From date] and [Enter To Date]" (without quotes)

So, now Create a Report based on this Query.

Whenever u open a report based on this query,
Automatically Access will ask
Parameter [Enter From Date]
Parameter [Enter To Date]
Feed these and press enter key respectively.

Next u will see is the report with what u want