By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,197 Members | 956 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,197 IT Pros & Developers. It's quick & easy.

Help trying to create a database in Access 2013

P: 9
Hi i am trying to create a database in Access 2013 to tracking telephone Billing Cost per user for different billing periods. I am really new to access. So I would really appreciate any help rendered.

I have the following tables

Departments
Employees
Manufacturer
Phone Details
Billing Details

Department has two Fields (Dept_ID and Dept_Name)

Employee has two (2) Fields (Empl_ID and Empl_Name)

Manufacturer has two (2) Fields (Mfr_ID and Mfr_Name)

Phone Details has twenty-three (23) Fields
ID, Description, Manufacturer, Model, Serial Number, SIM_Card_Type, SIM_Card_Num, IMEI_Num, Tele_Num, Warranty, Vaild_True, Assign_To, Department, Acquisition Date, Cost, Photograph, Mobile_Plan, Mobile_Plan_Cost, CUG, CUG_Cost, Allowed Plan Cost, Credit Limit, Account Status


Billing Details has eleven (11) Fields
(Billing_ID, Assigned_To, Tele_Num, Invoice_Num, Invoice_Date, Service_Period_Start_Date, Service_Period_End_Date, Current_Charge, VAT, Total_Charge, Under_Over_Stated_Cost


So i've created a form for user to enter data for the Phone Details. I have also created one for user to enter data for Billing Details for Information from the actual invoice.

I need to create a report now that will show Billing_ID, Assigned_To, Tele_Num, Invoice_Num, Invoice_Date, Service_Period_Start_Date, Service_Period_End_Date, Current_Charge, VAT, Total_Charge, Allowed Plan Cost, Credit Limit and Under_Over_Stated_Cost

Allowed Plan Cost and Credit Limit would be found in Phone Details Table and Under_Over_Stated_Cost is equal to Current_Charge minus Allowed Plan Cost

Can someone please help with this.

Thanks

Lisa
Dec 28 '16 #1
Share this Question
Share on Google+
15 Replies


gnawoncents
100+
P: 214
What field overlaps between the Phone Details and Billing Details Tables? In order to create a report that pulls pertinent information from two separate tables, you need something that links them. For example, if the database will contain more than one customer's information, the Billing_ID field might be included in both so you can pull the appropriate records. Then it would be simple to create a query, linking the two tables and including all the pertinent fields from each, that you could use as the source for your report.

If I misunderstood your problem, please let me know how the tables are being used and I can offer a more specific solution.
Dec 28 '16 #2

P: 9
i'm not sure i understand....but for both tables the ASSIGNED TO Field overlaps i think.
Dec 29 '16 #3

gnawoncents
100+
P: 214
So, the ASSIGNED TO field is what you would use to select a customer and run the report? If so, I'm assuming it contains a unique value (e.g. there wouldn't be two SMITHs in the same table for that field), so you can create a query using your Phone Details and Billing Details tables and join them with the ASSIGNED TO field. Select to show only the rows where the joined fields from both tables are equal.

You can now use that Join Query (instead of a table) as the source for your report.

Again, I'm making some assumptions here because not all the information is clear, so please let me know if your database is set up differently than I described.
Dec 29 '16 #4

P: 9
i am still not unsure...can i email a copy of what i've done so far for u to analyse and assist?
Dec 29 '16 #5

gnawoncents
100+
P: 214
I'd be happy to take a look. You should be able to attach it as screenshots or zip and attach it to your post (by editing the original question).
Dec 29 '16 #6

P: 9
Please see file attached
Dec 30 '16 #7

P: 9
Please send an email address as i am unable to attached file...thanks
Dec 30 '16 #8

gnawoncents
100+
P: 214
You can't do it on the internal Bytes email but can on the original post or a reply. Click on Go Advanced --> Manage Attachments, then Browse and Upload the attachment(s).
Dec 30 '16 #9

P: 9
it's not uploading....not sure why
Dec 30 '16 #10

gnawoncents
100+
P: 214
Check the file size and type. If those are within what is allowed, I'm not sure why it isn't working. Unfortunately I only use Mac's at home (no MS Access), and my work email would likely strip the attachment. I've attached a screenshot of what I'm talking about in regards to the query.
Attached Images
File Type: png Query Example.png (21.3 KB, 63 views)
Dec 30 '16 #11

NeoPa
Expert Mod 15k+
P: 31,494
Lisa.

You may find Attach Database (or other work) helpful.

Requesting email addresses here is strongly frowned upon (I know you're new so we quite understand you wouldn't know that). Feel free to let me know if you continue to have difficulties uploading your work.

A reply in here will get my attention if you're not familiar with the PM system yet, though that would be preferred obviously.
Jan 4 '17 #12

P: 9
See Attached. Thanks NeoPa. I was attaching the files in the wrong format. I zipped and was able to attached.
Attached Files
File Type: zip Cell Phone Inventory Work in Progress.zip (148.6 KB, 54 views)
Jan 4 '17 #13

P: 9
Any help...i am so lost as to why my fields not linking
Jan 4 '17 #14

P: 9
I started over from scratch and got it to work....thanks... not sure what i was doing wrong but someone can still check and advise
Jan 4 '17 #15

NeoPa
Expert Mod 15k+
P: 31,494
Hi Lisa.

I'm very pleased you got it to work.

I can't say no-one will be interested in checking out your project for you, but I will say that I'd expect that to take quite a bit of time and effort. GnawOnCents has been involved and may still be interested, but just bear in mind it would be quite a commitment for not too much benefit. Not many have that amount available for a single question.

I expect you're already starting to progress from where you were just a short while ago. While it may feel a little like swimming through treacle at this stage, you've already shown you can make progress under difficulty.

You may find going through some of Crystal's tutorial work (Microsoft Access Tutorials (Strive4Peace)) to be helpful. I expect this would be more use to you right now than waiting for someone to debug your earlier work.

All the best -Ade.
Jan 5 '17 #16

Post your reply

Sign in to post your reply or Sign up for a free account.